PDA

View Full Version : Solved: script to highlight autofilter header



aloy78
10-05-2011, 01:44 AM
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
10-05-2011, 02:19 AM
Doesn't the little filter symbol already do that for you?

farhanjaved
10-05-2011, 07:46 AM
what version are you using?

aloy78
10-05-2011, 06:50 PM
Hi xld,

Yes. That is indeed true. But my sheet consist of a lot of columns (currently working at 57 over columns and counting) :mkay 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
10-05-2011, 08:46 PM
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
10-06-2011, 08:33 PM
Hi Dave,

Thanks. Exactly what i was looking for. :thumb