Excel Auto Filter

[Dickx]

Using Excel's auto filter function, how can I change the color of the drop down filter or the column that I am filtering.

 

[GeekGirlau]

Copy the following code to the worksheet Selection Change event.

    Dim rng As Range
    
    
    If ActiveSheet.AutoFilterMode = True Then
        For Each rng In Range("A1", Range("A1").End(xlToRight))
            With ActiveSheet.AutoFilter.Filters(rng.Column)
                If .On Then
                    rng.EntireColumn.Interior.ColorIndex = 6
                Else
                    rng.EntireColumn.Interior.ColorIndex = xlNone
                End If
            End With
        Next rng
    End If

There is a restriction here - the columns will only change colour if you click on a different cell. Selecting a different item in the drop-down filter will not trigger the code unless you also click somewhere else on the sheet.

 

[GeekGirlau]

Dickx, just found the following knowledgebase article which solves the restriction I mentioned above:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=365