how to shade a filtered list using code?

[dmasvar]

hi,

after a user has filtered a list, is there code to have it shade the lis starting from a certain cell and letting it determine what the end cell should be, the bottom right cell dependiog on the what was filtered..?

using code?

is this possible.

 

[Damon Ostrander]

Hi smasvar,

Try this macro:

Sub ColorFilteredRows()
Dim iRow As Long
Dim UpperLeftCell As Range 'the upper left corner of the filtered range
Dim LastRow As Long
Dim LastCol As Integer
Set UpperLeftCell = Range("B2")
LastRow = Cells(65536, UpperLeftCell.Column).End(xlUp).Row
LastCol = Cells(LastRow, 256).End(xlToLeft).Column
For iRow = UpperLeftCell.Row + 1 To LastRow
If Not Rows(iRow).Hidden Then
Range(Cells(iRow, UpperLeftCell.Column), Cells(iRow, LastCol)).Interior.ColorIndex = 6
End If
Next iRow
End Sub

You should set the UpperLeftCell range to the cell at the top left corner of the filtered range (should be in the row containing the filter dropdowns).

This macro assumes that all cells in the filtered range contain data (no empty cells). If you have empty cells, it becomes a bit more involved.

Keep Excelling.

Damon