Highlighting filtered columns - automatically

[wilsojer]

I have been trying to find a way to highlight columns that are filtered in Excel. On a laptop it is not always that easy to tell which columns are filtered. I have found a piece of VBA that can do this:

'This sub goes in code pane for the worksheet containing AutoFilter data
Private Sub Worksheet_Calculate()
ColorAutoFilter
End Sub

'This sub goes in a regular module sheet
Sub ColorAutoFilter()
'This sub should be triggered by the recalculation of the worksheet and then called by a
'Worksheet_Calculate sub. You can force a recalculation after an AutoFilter by using the "trick"
'of including at least one volatile cell in your worksheet. A volatile cell has a formula using
'volatile functions like NOW, TODAY, OFFSET, INDIRECT, RAND, INFO or CELL (except when CELL
'uses the "filename" parameter).
'If more than one worksheet uses AutoFilter, then each sheet's Worksheet_Calculate sub may call ColorAutoFilter
Dim FilterNum As Long
With ActiveSheet
If .AutoFilterMode Then
For FilterNum = 1 To .AutoFilter.Filters.Count
If .AutoFilter.Filters(FilterNum).On Then
.AutoFilter.Range.Columns(FilterNum).Interior.ColorIndex = 6 'yellow
Else
.AutoFilter.Range.Columns(FilterNum).Interior.ColorIndex = xlNone
End If
Next
Else
.Cells.Interior.ColorIndex = xlNone
End If
End With
End Sub

I would like to do two things:

1. Have this run whenever an autofilter is changed (a new filter chosen or a current filter changed).

2. Have this effective for every workbook that I open.

I'm hoping that someone can help a VBA newbie sort this out.

Thanks in advance,

Jeremy

[Bob Phillips]

Add a formula to a worksheet that has autofilter like so

=showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+32)

where L is the filtered colum. This will trigger the calculate.