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
Bookmarks