Alternate shading based on series of numbers in 3 columns

[Ozbobeee]

Hi,

XL 2003.

I have a range that spreads over Cols A:H, with a dynamic number of
rows, starting at Row 15.

The range is sorted into numeric order based on Cols F - H.

I wish to employ alternate shading (Cols A - H) starting at Row 15,
based on each series identified in Cols F - H (these are dynamic), eg
(Note that I have only exampled Cols F - H).

Col F Col G Col H
R15 1 0 0 (No shade)
R16 1 0 0 (No shade)
R17 1 0 0 (No shade)
R18 1 1 0 (Shading)
R19 1 1 1 (No shade)
R20 1 1 1 (No shade)
R21 2 0 3 (Shading)
R22 2 0 3 (Shading)
R23 4 0 0 (No shade)
etc
etc

I would like to automate this task, that is currently being done
manually.

Many Thanks

Bob
Maitland Australia

 

[Peter T]

Hi Bo,

It's not obvious what condition you want to shade your rows. The only thing
that appears in common in your example is if 2 of 3 values <> 0

If that guess is correct try conditional formatting. Select cell A15 and in
the CF dialog
select the "Formula is" setting

=(($F15<>0)+($G15<>0)+($H15<>0))=2

Paste special formats in over the range A15:Hx where x is the last row that
might need shading.

Regards,
Peter T

 

[Ozbobeee]

Tks for the reply, Peter.

To clarify, each row, starting at 15, is grouped together, based on
the values in Cols F - H eg.
All entries with 1,0,0 are grouped together, all with 1,1,0 are
grouped together etc.

The combination 0,0,0 will never show, nor will any negative values.


Rather than shade by alternate rows, I wish to have alternate shading
Cols (A - H) based on a change in sequence (groups) of the values in
Cols F - H.

The worksheet is used on a fortnightly basis at which time the
individual values in Cols F - H change. Thus the need for code.

Any assistance appreciated.

Cheers

Bob

 

[Peter T]

Hi Bob,

I follow now that you want alternate vertical shading in columns, but I
don't follow what criteria from values in Cols F-H defines whether or not to
apply banded shading. Also will all columns have the same shading or does
each row of alternate cells have its own banded shading.

Regards,
Peter T

 

[Ozbobeee]

Hi Peter,

I really haven't explained the situation that well, have I?

The dynamic range, starting at A15 was sorted numerically, based on
the corresponding values in Cols F, G, and H. Thus, in the example
Rows 15:17 were grouped because they all had the values 1, 0, 0 in the
corresponding cells in Cols F, G, and H.

The next grouping only had one row to it - Row 18 with values of 1, 1,
0..

The third grouping had two rows, 19 and 20 each with the values of 1,
1, 1.

Initially I wanted each alternate grouping of rows to be shaded.

I've had a re-think and decided that a better option may be to simply
have code that will insert a blank row between each of the groupings
and then shade these blank rows.

In essence I was simply trying to make the sheet easier to read for
data entry purposes, which the blank row scenario accomodates.

I really appreciate you taking the time to respond.

Thanks again.

Cheers

Bob

 

[Peter T]

Hi Bob,

OK I think I get it now. As much me having been slow to understand as you
not to have explained. <g>

Hopefully following will do what you originally asked for, ie the shading

Option Explicit
Sub test()
Dim b1 As Boolean, b2 As Boolean
Dim rbRow As Long
Dim rtRow As Long
Dim i As Long
Dim v
Dim rng As Range

With Range("F15")
rtRow = .Row
rbRow = .End(xlDown).Row
v = Range(Cells(rtRow, .Column), Cells(rbRow, .Column + 2)).Value
Set rng = Range(Cells(rtRow, 1), Cells(rbRow, .Column + 2))
End With
rtRow = rtRow - 1

rng.Interior.ColorIndex = xlNone
rng.Rows(1).Interior.ColorIndex = 6

b1 = True
For i = 2 To UBound(v)
b2 = v(i, 1) = v(i - 1, 1) And _
v(i, 2) = v(i - 1, 2) And _
v(i, 3) = v(i - 1, 3)

If b1 = b2 Then
Range(Cells(rtRow + i, 1), Cells(rtRow + i, 8)).Interior.ColorIndex = 6
End If

If b2 = False Then
b1 = Not b1
End If
Next

End Sub

If you want to insert rows beteen bands instead of shading, adapt the above
but loop from the bottom, ie

For i = ubound(v) - 1 to 1 step -1
and compare v(i, 1) = v(i +1, 1) , etc
When you get to a "switch" insert a row and continue

Regards,
Peter T

 

[Ozbobeee]

Hi again Peter,

The code worked a treat.

I really do appreciate the time and effort you have put in.

We got there in the end..... <g>

Many Thanks,

Cheers

Bob