COLOR CODE WITH CALCULATE EVENT (Autofilter)

COLOR CODE WITH CALCULATE EVENT

This one can be used in addition to the custom function above, or on its own. However, you really should have at least 1 volatile function on the Worksheet it is used in. To ensure this simply Enter =TODAY() in any cell. Right click on the Worksheets name tab, choose View Code and in here paste the exact code below.

Private Sub Worksheet_Calculate()
Dim lFilt As Long, lFiltArrows As Long
Dim lFiltRow As Long
On Error Resume Next
Application.EnableEvents =
False
lFiltRow = Me.AutoFilter.Range.Row
lFiltArrows =
Me.AutoFilter.Filters.Count

Range(Cells(lFiltRow, 1), Cells(lFiltRow, _
lFiltArrows)).Interior.ColorIndex = xlNone
If Me.FilterMode = True Then
For lFilt = 1 To lFiltArrows
If Me.AutoFilter.Filters.Item(lFilt).On
Then
Cells(lFiltRow, lFilt).Interior.ColorIndex = 46
End If
Next
lFilt
End If
Application.EnableEvents = True
On Error GoTo 0
End
Sub

Come back to Excel and again filter your table. You will note that this one will automatically detect your headings which have AutoFilter applied. When no criteria is set, in others words not filtering, no color will change.