Hi everyone,

I'm trying to insert some tab code so that whenever I apply a filter to a column in the second row (the first row is full of aesthetic stuff, don't ask why, company file), the head cell changes colour, but then reverts back to it's original colour. I've found a few codes that change the colour when a filter is applied, this isn't the issue. I just don't know what VBA code to write so that excel restores the original colour of the cell once the filter is removed.

Thanks, any help will be much appreciated!

The code is below.

Private Sub Worksheet_Calculate()
' Declare Variables.......... 
  Dim af As AutoFilter
  Dim fFilter As Filter
  Dim iFilterCount As Integer
 
' When active sheet has autoFilter mode on then........????
  If ActiveSheet.AutoFilterMode Then
    Set af = ActiveSheet.AutoFilter
   iFilterCount = 1
    For Each fFilter In af.Filters
   ' If Filter on then change selected filter colour index to yellow (ColorIndex = 6)....   
        If fFilter.On Then
        af.Range.Cells(1, iFilterCount) _
          .Interior.ColorIndex = 6
      Else
        af.Range.Cells(1, iFilterCount) _
          .Interior.ColorIndex = xlNone
      End If
      iFilterCount = iFilterCount + 1
    Next fFilter
  Else
'Otherwise, no color change...
Rows(1).EntireRow.Interior.ColorIndex = xlNone
  End If
End Sub