Color rows by "group"

[CLR]

Hi All.....
I would like to alternate the row color from gray to none, for each group of
rows according to the value in the cells in column G. That is, starting with
row 11, I want no background color, then if G12=G11, likewise row 12 would
have no color, but if G12<>G11, then the background color of row 12 would
switch to gray. Likewise progressing down column G, as the values change
from the one above it, so does the row color, if the values are the same as
the one above, the row color would stay the same as the one above. The
entire column is sorted in order so there would be no like values showing up
out of order. The length of column G will be unknown.

Example
row Col G Color
11 aa none
12 aa none
13 bb gray
14 cc none
15 cc none
16 cc none
17 dd gray
18 dd gray
19 ee none

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Nigel]

Try this.......I set it to process Sheet1, change as required.

Sub Shader()
Dim c As Range, xc As Integer
xc = 15
With Sheets("Sheet1")
For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
' toggle fill
If c.Value <> c.Offset(-1, 0).Value Then
If xc = 0 Then xc = 15 Else xc = 0
End If
' set fill
With .Rows(c.Row).EntireRow.Interior
.Pattern = xlSolid
.ColorIndex = xc
End With
Next c
End With
End Sub

 

[CLR]

Nigel, that is "'way beyond COOL", that is "OUTSTANDING TO THE MAX".

Thank you many, many times.........

Vaya con Dios,
Chuck, CABGx3

 

[Nigel]

Glad to be of help

--

Regards,
Nigel
(e-mail address removed)

 

[CLR]

Now a follow on question, if you please........
Can this nifty code you provided be modified to work only on the visible
cells in an Autofiltered display?

TIA
Vaya con Dios,
Chuck, CABGx3

 

[Nigel]

In its current form No. This would a a more complex requirement for a
number of reasons

1. The testing of the previous row would no longer be a valid test, in other
words the offset would be variable based on the last visible row.
2. The current process scans the entire range in column G, it would need to
skip hidden rows, that is those that are filtered
3. Also need to consider changes to autofilter settings to recompute the
shading dynamically.

There is a test to check if a row is hidden (filtered) ......
c.EntireRow.Hidden ; where c is a range object representing a single cell.

Process would look like this.....

Store first non-hidden row test value in a variable
Check if next non-hidden row is different, toggle color index and update the
test value var - if different
Fill non-hidden row with the color index setting

My first attempt........you could arrange for this to be run whenever a new
filter setting was made, it does not clear the fills for hidden rows!

Sub Shader2()
Dim c As Range, cTest, xc As Integer
xc = 0: cTest = ""
With Sheets("Sheet1")
For Each c In .Range("G11:G" & .Cells(.Rows.Count, "G").End(xlUp).Row)
If Not c.EntireRow.Hidden Then
' get first non hidden row test value
If cTest = "" Then cTest = c.Value

' toggle fill
If c.Value <> cTest Then
If xc = 0 Then xc = 15 Else xc = 0
cTest = c.Value
End If

' set fill
With .Rows(c.Row).EntireRow.Interior
.Pattern = xlSolid
.ColorIndex = xc
End With

End If
Next c
End With


--

Regards,
Nigel
(e-mail address removed)

 

[CLR]

ABSOLUTELY AMAZING NIGEL!!!!!!
Your new code works both with the Autofilter on, and with it off.....double
bonus!
Your extreme talent and kindness has made my day.
Thanks again, ever so much.

Vaya con Dios,
Chuck, CABGx3