Have you ever been using a large Excel file and wondered which cell am I actually filtering here? Especially if you pick up someone else's file. There might be more than one column filtered and there may be hundreds of columns. You tend to go blind after a brief period of looking for that column with a blue arrow on the filter. Well there is an alternative. What if every time you filtered the worksheet a colour appeared in the cell(s) you were filtering. You could see instantly which column was the source of the filter.
The code below will filter any column in light yellow. It is placed in the worksheet module.
Option Explicit
Private Sub Worksheet_Calculate()
Dim i As Long
Rows("1:1").Interior.ColorIndex = xlNone
If Sheet1.AutoFilterMode Then
With Sheet1.AutoFilter
For i = 1 To .Filters.Count
If .Filters(i).On Then .Range(1, i).Interior.ColorIndex = 19
Next i
End With
End If
End Sub
The VBA code must go in the worksheet which you are running the code from. So the question to ask yourself is which sheet has the filtered list.
So if the sheet which was being filtered was Sheet1 then paste the code into sheet1 visual basic editor pane.
If you wanted a different colour, suppose yellow does not go with your workbook colour scheme what you might wish to do is run this vba procedure. It is how I chose my light yellow colour.
Sub YourColours()
Dim i As Integer
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i
End Sub
If you run this colour code it will place all of the colours from Index from A1 to A56. There are only 56 colours used by index number (obviously there are limitless colours but with this method you only get 56 colours. If any of the colours take your fancy from this VBA script then take note of the row number and replace the 19 here.
af.Range.Cells(1, j).Interior.ColorIndex = 19
with your new row number. Hope this helps.