Filter on Color
 

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

Filter on Color
 

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


"ssGuru" wrote:

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

Filter on Color
 

On Sep 6, 4:56 pm, Gary''s Student
wrote:
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



"ssGuru" wrote:
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- Hide quoted text -

- 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