Check to see if Autofilter is Engaged

[asmithbcat]

In Excel 2003....

I have been asked to look into changing the color of the arrow on an
autofilter tab for a filtered cell from blue to some other color so it can be
more easily distinguished from the black tabs which are not filtered.

I know the actual color of the arrow cannot be changed. However, is there
some formula (like ISERROR() only ISFILTERED()) which can be used to see if
an autofilter is engaged on a particular cell?

I have also tried using VBA and conditional formats to change the color of
the Header cell when a filter was being used, but this is a shared workbook,
and when I was testing it out, I received an error(However, it did work when
the workbook was not shared).

Any help or suggestions would be much appreciated.

 

[Dave Peterson]

You may find that looking at the row numbers of the autofilter range is easier.
They change to blue, too.

Did you look at Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html#Filter
and download the file in this section:
FL0014 - Colour Filter Headings

To see if that helps???

 

[asmithbcat]

Dave,

I did take a look at Debra's site. I tried using this code on my workbook,
but when I tested it out as a shared workbook, I received an error message,
and the program would no longer work. Are there any restrictions from this
example which would not be allowed in shared workbooks?

I can copy the code in if you would like.

 

[asmithbcat]

Dave,

I actually referred to the UDF described in FL0018 - Highlight Filtered
Headings in List. I must have overlooked FL0014 when originally looking
through the site. Do you think an error may still occur if the workbook is
shared?

 

[Dave Peterson]

Did you try to add the conditional formatting while the workbook was still
shared?

That won't work.

But you could unshare it, add the conditional formatting, reshare it and you
should see the formatting change in that shared workbook.

 

[Dave Peterson]

Or maybe you could plop a value above the header with the filter criteria for
that field.

 

[Dave Peterson]

I downloaded Debra's FL0018 workbook and removed the Data|List (Convert to
Range) so I could share the workbook.

Then I shared the workbook, filtered some columns and everything worked fine.

 

[asmithbcat]

Thanks Dave,

I think it is solved.

I downloaded Debra's FL0018 workbook and removed the Data|List (Convert to
Range) so I could share the workbook.

Then I shared the workbook, filtered some columns and everything worked fine.