Highlight the header being used to AutoFilter a range

[allanhoey]

hi I have been trying to modify this code below to my requirements, this code I found on a previous post highlights the full column and I am trying to modify it so that it will only highlight the Header being used in the autofilter but I have been unsuccessful, I also want this to work from an excel add on so it can be used on any active workbook or sheet,


is this possible ?

this original was submitted by "byundt"

'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

[Aflatoon]

Change this part:
If .AutoFilter.Filters(FilterNum).On Then
.AutoFilter.Range.Columns(FilterNum).Interior.ColorIndex = 6 'yellow
Else
.AutoFilter.Range.Columns(FilterNum).Interior.ColorIndex = xlNone
End If
to this:

If .AutoFilter.Filters(FilterNum).On Then
.AutoFilter.Range.Columns(FilterNum).cells(1).Interior.ColorIndex = 6 'yellow
Else
.AutoFilter.Range.Columns(FilterNum).Cells(1).Interior.ColorIndex = xlNone
End If

[allanhoey]

Thank you, works a treat,

is it possible to add code or maybe a loop that will allow this to work with any workbook e.g changing the private Sub part to look for and calculate on any open workbook

my intention is to have this always available as an Add in

Private Sub Worksheet_Calculate()
ColorAutoFilter
End Sub

[Aflatoon]

Yes - you will need an application-level event handler. See Chip Pearson's page here (http://www.cpearson.com/Excel/AppEvent.aspx).

[allanhoey]

Hi thanks for the info, i have read this page and i am stump t

I created a application level macro using the class module and normal module to bring up a msg box every time a page was resized, I found a step by step on a Microsoft help page that took me through it as the cpearson page was out of my league, I must have been missing something to get the Application Events In An Existing Object Module part working

it says it works like a workbook object but I am new to this and unfortunately am struggling to relate this to my project

am I correct in assuming it is the book 1 worksheet object I need to link to the module ColorAutoFilter(), but using the app object the, Worksheet_Calculate() needs to only run once a new workbook has opened or I receive an error when i save as a .xla Add In?