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 Removed)
"CLR" <(E-Mail Removed)> wrote in message
news:53F571F7-71DD-4C5B-ACC1-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>> --
>>
>> Regards,
>> Nigel
>> (E-Mail Removed)
>>
>>
>>
>> "CLR" <(E-Mail Removed)> wrote in message
>> news:5CB95A2C-F761-4649-8F15-(E-Mail Removed)...
>> > 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
>> >
>> >
>>
>>
Similar Threads
