AutoFilter Tabs

[FeFi]

I use Data>Filter>AutoFilter frequently for multiple columns. When a filter
has been used on any column the "triangle" marker on the tab changes from
black to blue. Because I frequently work with my sheets at 75% I have a
problem distinguishing one color from another so can't always find the
columns I've filtered. It would be very helpful if the entire tab changed
color, or if that 's not possible, change the "triangle" to a bright color
such as red, pink, or orange.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...0-3cffc5f33bfb&dg=microsoft.public.excel.misc

 

[Jim May]

Paste the below code into the Sheet module of the appropriate sheet; then
in a blank cell enter =Now() - to call the Calculate event which will run
the code

Private Sub Worksheet_Calculate()
Dim af As AutoFilter
Dim fFilter As Filter
Dim iFilterCount As Integer

If ActiveSheet.AutoFilterMode Then
Set af = ActiveSheet.AutoFilter
iFilterCount = 1
For Each fFilter In af.Filters
If fFilter.On Then
af.Range.Cells(1, iFilterCount) _
.Interior.ColorIndex = 6
Else
af.Range.Cells(1, iFilterCount) _
.Interior.ColorIndex = xlNone
End If
iFilterCount = iFilterCount + 1
Next fFilter
Else
Rows(1).EntireRow.Interior.ColorIndex = xlNone
End If
End Sub

 

[FeFi]

Unfortunately my knowledge of the typeof code you furnished is basically
non-existent. What does this do? Is this a permanent fix for the color on
the AutoFilter tabs on all sheets? ? ? ? ? ? ?

 

[Gord Dibben]

Jim may be otherwise engaged.

What he has posted is worksheet event code which is triggered by the =NOW()
formula when you click on an Autofilter dropdown arrow.

The cell with that arrow will be highlighted in yellow.

Copy the code then right-click on the sheet tab and "View Code".

Paste into that sheet module.

Alt + q to return to the Excel window.

Click on one of the AF arrows and select something to filter on.


Gord Dibben MS Excel MVP

 

[FeFi]

Thanks for the explanation. I copied and pasted the code as instructed but
when I enter the =NOW() formula I get:

Visual Basic "Compile error: Syntax error" with the
" Set af = ActiveSheet.AutoFilter" line of code highlighted.

I assume it's something minor but I don't know how to fix it.

 

[Gord Dibben]

You pasted Jim's code into the worksheet module as written?

From here I can't tell what the pasted code looks like so hard to troublesheet.

Syntax errors usually mean a spelling mistake or one long line wrapping to 2
lines.

When you pasted in did you see any lines of red text which would indicate a poor
copy/paste?

I copied directly from Jim's post and the code worked "out of the box" for me.

If you can't see anything obvious, email the workbook to me.

Change the AT and DOT for my email address.


Gord

 

[FeFi]

I couldn't see anything obvious so decided to delete everything and try the
copy and paste again in my trial sheet. I got the same result, so I deleted
the entire file. I then opened another trial file, did the copy & paste as
instructed, and the code worked "out of the box" for me as well.

Even though this works well for existing data, and can certainly be applied
on an "as needed" basis, I'd still like to see the default "blue" triangle
changed to another color in the future - my eyes just keep getting older!

Thanks so much for your patience and continued suggestions.

 

[Gord Dibben]

Maybe in version 14 of Excel.


Gord

I couldn't see anything obvious so decided to delete everything and try the
copy and paste again in my trial sheet. I got the same result, so I deleted
the entire file. I then opened another trial file, did the copy & paste as
instructed, and the code worked "out of the box" for me as well.

Even though this works well for existing data, and can certainly be applied
on an "as needed" basis, I'd still like to see the default "blue" triangle
changed to another color in the future - my eyes just keep getting older!

Thanks so much for your patience and continued suggestions.