Filter highlighter

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Filter highlighter

Post by 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.
Regards
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter highlighter

Post by 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
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Filter highlighter

Post by dasadler »

How do you use a XLA file?
Don

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Filter highlighter

Post by Don Wells »

dasadler wrote: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.
Regards
Don

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Filter highlighter

Post by Don Wells »

HansV wrote:It was created by Rory in what was then Woody's Lounge.
I have attached the version that I have.
Thank you Hans. :thankyou:
Regards
Don

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Filter highlighter

Post by 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.
Don

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter highlighter

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter highlighter

Post by 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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Filter highlighter

Post by 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.
Last edited by dasadler on 24 May 2012, 13:39, edited 1 time in total.
Don

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Filter highlighter

Post by rory »

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

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter highlighter

Post by HansV »

Hi Rory,

That would be very nice!
Best wishes,
Hans

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Filter highlighter

Post by 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?
Regards
Don

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Filter highlighter

Post by 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

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter highlighter

Post by HansV »

Don Wells wrote: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...
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Filter highlighter

Post by 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.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter highlighter

Post by HansV »

bradjedis wrote: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?
Best wishes,
Hans

bradjedis
4StarLounger
Posts: 536
Joined: 30 Mar 2010, 18:49
Location: United States

Re: Filter highlighter

Post by 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

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter highlighter

Post by HansV »

Yes, I fear so.
Best wishes,
Hans

User avatar
Don Wells
5StarLounger
Posts: 689
Joined: 27 Jan 2010, 16:45
Location: Ottawa, Ontario, Canada

Re: Filter highlighter

Post by Don Wells »

:blush:
HansV wrote:You may have done something similar in reply #34 in Auto-filter-spinner-controls...
:thankyou: :thankyou: :thankyou: :thankyou: :thankyou: :blush:
Regards
Don

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Filter highlighter

Post by rory »

Revised version with its own ribbon tab and buttons.
You do not have the required permissions to view the files attached to this post.
Regards,
Rory