Filter on Color

[ssGuru]

I have code that highlights cells in certain records based on my
rules. I have code that removes these highlights.

Works just great.

Now I would like to be able to create a filter that will JUST show the
rows where one or more cells are highlighted. I am currently using
only 3 colors, Light Yellow, Lavender and Pink for the highlighted
cells.

I haven't found any clever way to display just the rows WITH
highlighted cells.

Any ideas,
Dennis

 

[Guest]

This macro will examine each row in ActiveSheet.Usedrange (extended) and hide
rows which are totally colorless.:

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.Count
If Cells(rr, cl).Interior.ColorIndex <> xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

 

[ssGuru]

This macro will examine each row in ActiveSheet.Usedrange (extended) and hide
rows which are totally colorless.:

Sub color_shower()
Dim b As Boolean

Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1

For rr = 1 To nLastRow
b = True
For cl = 1 To Columns.Count
If Cells(rr, cl).Interior.ColorIndex <> xlNone Then
b = False
End If
Next
If b Then
Cells(rr, 1).EntireRow.Hidden = True
End If
Next
End Sub

--
Gary''s Student - gsnu200743









- Show quoted text -

Thanks Gary's Student, the code looked good but all I got was the for
next loop running endlessly using F8. The nLastRow value shows the
correct count of 12 rows in the sample sheet. But one of the rows had
a cell with Light Yellow and the code should have filtered to display
only that one row???

Any suggestions?
Dennis