Question:
When filtering a column, the header row is to be highlighted with a yellow background color
Answer:
Insert the following code in the appropriate modules.
Place the code below into the standard module.
Private Sub Worksheet_Calculate()
Dim flt As Filter
Dim intCol As Integer
For Each flt In ActiveSheet.AutoFilter.Filters
intCol = intCol + 1
If flt.On Then
Cells(1, intCol).Interior.ColorIndex = 6
Else
Cells(1, intCol).Interior.ColorIndex = xlColorIndexNone
End If
Next flt
End Sub
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"Hello,
Unlike VB, its bigger brother, VBA lacks any decent timer event (and could use a few more events of any kind), which would have been a surefire way to make this macro work. However, I have been able to achieve decent results with this setup:
Simply put, assigning the most events possible to a call going to the filter highlight sub if the closest we can get to a timer event AFAIK.
In a module, put:
Sub filterhighlight()
Dim flt As Filter
Dim intCol As Integer
For Each flt In ActiveSheet.AutoFilter.Filters
intCol = intCol + 1
If flt.On Then
Cells(1, intCol).Interior.ColorIndex = 6
Else
Cells(1, intCol).Interior.ColorIndex = xlColorIndexNone
End If
Next flt
End Sub
In every worksheet where you want to use the macro:Private Sub Worksheet_Activate()
Call filterhighlight
End Sub
Private Sub Worksheet_Calculate()
Call filterhighlight
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Call filterhighlight
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call filterhighlight
End Sub
"
"Hi,
I've been searching for something to do this for ages. The Autofilter drop-down arrowhead is virtually indistinguishable when blue (filter on) as opposed to black (filter off), so being able to highlight the header cell is a real help. Would it be possible to have the highlight turn on and off automatically whenever the autofilter function is used?. At the moment it seems you have to manually run the macro each time a new filter is set, and then again when it is cleared.
A great help anyway. Thanks."