Filter rows based on Color

Sometimes we work with excel sheets that have color coded rows and we often want to look at only a particular color at a time.

Following vba code does just that. Select any one cell of the color that you want to see. And run the macro filter_on_color. Make sure that column EZ is blank.

Sub filter_on_color()
' Select any color based on which to filter the sheet
' make sure EZ is empty

colindex = Selection.Cells.Interior.ColorIndex
col = ColumnLetter(Selection.Column)
lastrowcnt = Cells(Cells.Rows.Count, "A").End(xlUp).Row

MsgBox "Filtering on cell " & (col & Selection.Row)

For i = 1 To lastrowcnt
If Range(col & i).Cells.Interior.ColorIndex = colindex _ 
Then
Range("EZ" & i) = "Filter on color"
Else
Range("EZ" & i) = ""
End If
Next i

ActiveSheet.Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=156,  _ 
Criteria1:="Filter on color"
Range("A1").Select

End Sub

---------------------------------------------------------------------------------------

Function ColumnLetter(ColumnNumber As Integer) As String
' This function is taken from 
' http://www.freevbcode.com/ShowCode.asp?ID=4303

If ColumnNumber > 26 Then

' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter

' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')

ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function