Change cell colour when applied filter is on

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

Isn't this a duplicate of the thread you started less than an hour ago?

Pete

Yes unfortunately it is, I thought I had started it, but when I went to check my previous posts, it didn't come up, so I assumed that I had mis-clicked somewhere and had not started the thread.

If someone can remove the new thread I would be very grateful.

Okay, bring up your first thread and click the warning triangle in the bottom left of the post to report it to the moderators - leave a message asking them to remove the duplicate.

Hope this helps.

Pete

Thanks, I've done that.

So, does anyone know what to do regarding my problem?