Filter highlighter

[Don Wells]

As a consequence of a failed hard drive, I have lost an add-in which highlighted the heading of the column being filtered. I thought that it came from John Walkenbach's page; but I am unable to find it either in my backup or on John's page. Does anyone have info that would assist?

T.I.A.

[HansV]

It was created by Rory in what was then Woody's Lounge.
I have attached the version that I have.

Added later: see below for an updated version of the add-in

[dasadler]

How do you use a XLA file?

[Don Wells]

How do you use a XLA file?

An xla file is an Excel VBA project that has been saved as an Add-In. Although they occasionally come with instructions, they are usually quite intuitive -- as in this case. The instructions for loading them can be found here.

Edited to add this tutorial on add-ins.

[Don Wells]

It was created by Rory in what was then Woody's Lounge.
I have attached the version that I have.

Thank you Hans.

[dasadler]

Thanks to all. I think this presumes that the filters will be applied to Row 1. Sometimes I use Row 1 to contain criteria used for formulas below and I have my column headings on Row2. I notice that when I use autofilter, there is no obvious change in the column headings in Row2.

[HansV]

You must turn on AutoFilter first.
If you have data in the row(s) immediately above the column headings, you must take care to select the correct range before turning on AutoFilter, otherwise Excel will include the rows above the column headings in the filter range.

Then turn on filter highlighting by clicking "Highlight filters" on the custom toolbar (in Excel 2007 or later, this toolbar is displayed in the Add-ins tab of the ribbon.
The add-in will color a column heading if the range is filtered on that column.

x1095.png

[HansV]

I noticed that although the add-in as posted before correctly handled an autofilter range that does not start on row 1, it failed if the autofilter range does not start in the first column. The attached version corrects that. If you have already installed the previous version, you can simply overwrite Hilite Filters.xla with the new version (make sure that Excel is not running while you do this).

Hilite Filters.zip

[dasadler]

That was my problem... when I turned on Autofilter, I had my cursor in a column where one of my criteria was in row 1 of that column. Works as it should now.

[rory]

Thanks for fixing that, Hans. When I get a sec I'll make a 2007 version with proper Ribbon-y buttons.

[HansV]

Hi Rory,

That would be very nice!

[Don Wells]

Running Office 2003; I considered revising the code to replace the command bar with a pull-down button on the menu bar, but was intimidated by the need to make registry changes.

Is anyone out there interested in auditing and testing some air code when/if I think that I have it correct?

[bradjedis]

Greetings All,
Since I work at a company that will not allow us to download stuff to install/run (like the xla in this thread) I found (can't remember where or I would give credit) an alternative to this. However I wanted to make the code available all the time, as in being in the personal.xlsb file. I am not able to git it to work unless I have the code in the module of the workbook in use.

basically, you select the header row that will be used to filter, put the function call in the conditional formatting as a formula.

formula is =filteron(a3) then select the color for the formatting.

Here is the VBA I found. If I put the below in personal.xlsb the call appears to not work. Any thoughts?
Thanks,
Brad



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

[HansV]

Running Office 2003; I considered revising the code to replace the command bar with a pull-down button on the menu bar, but was intimidated by the need to make registry changes.

Is anyone out there interested in auditing and testing some air code when/if I think that I have it correct?

You may have done something similar in reply #34 in Auto-filter-spinner-controls...

[bradjedis]

After reading those posts, I would have to say no. I did not load the xla into my system. All I am asking is how to execute the code by making the call to the vba in the personal.xlsb, It works fine if the code in in the open workbook module.

[HansV]

formula is =filteron(a3) then select the color for the formatting.

What happens if you use

=Personal.xls!FilterOn(A3)

in Excel 2003 or before, or

=Personal.xlsb!FilterOn(A3)

in Excel 2007 or later?

[bradjedis]

I get this the error:

" You cannot use references to other worksheets or workbooks for conditional formatting criteria."

Guess that is my answer. Will have to do it on a case by case.


Thanks,
Brad

[HansV]

Yes, I fear so.

[Don Wells]

You may have done something similar in reply #34 in Auto-filter-spinner-controls...

[rory]

Revised version with its own ribbon tab and buttons.