» Set up yellow background color for header in an AutoFilter table using VBA in Microsoft Excel

Question:
When filtering a column, the header row is to be highlighted with a yellow background color


Answer:
Insert the following code in the appropriate modules.






Place the code below into the standard module.

Private Sub Worksheet_Calculate()

   Dim flt As Filter

   Dim intCol As Integer

   For Each flt In ActiveSheet.AutoFilter.Filters

      intCol = intCol + 1

      If flt.On Then

         Cells(1, intCol).Interior.ColorIndex = 6

      Else

         Cells(1, intCol).Interior.ColorIndex = xlColorIndexNone

      End If

   Next flt

End Sub