Solved: script to highlight autofilter header

[aloy78]

Hi all,

I'm looking for a script that will highlight any autofilter header when the filter is applied. Let's say I have autofilter ON for cell B2, C2, G2. I want these row headers to be highlighted so I can know that there is an filter being applied there. Appreciate that.

[Bob Phillips]

Doesn't the little filter symbol already do that for you?

[farhanjaved]

what version are you using?

[aloy78]

Hi xld,

Yes. That is indeed true. But my sheet consist of a lot of columns (currently working at 57 over columns and counting) and that kinda makes it hard to spot on directly when you have several filters on at the same time. Makes is quite taxing on the eye.

So I if I can work out a header highlight, I can see instantly that there is a filter on in that particular column. I do hope you get what I mean.

[Dave T]

Hello alloy78,

I found this on 'Andrew's Excel Tips' (http://blog.livedoor.jp/andrewe/archives/19415538.html) and have found it very useful.
If applied to the heading cell/s where the AutoFilter is applied the cell/s background changes to whatever colour you have chosen.

Enter the following in a standard module:

Function FilterOn(myCell As Range) As Boolean
  On Error Resume Next
  With myCell.Parent.AutoFilter
    With .Filters(myCell.Column - .Range.Column + 1)
      If .On Then FilterOn = True
    End With
  End With
End Function

Then using Conditional Formatting, enter =filteron(your cell reference). (Format > Conditional Formatting > Formula Is).

Regards,
Dave T

[aloy78]

Hi Dave,

Thanks. Exactly what i was looking for.