allanhoey
11-04-2011, 03:31 AM
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
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