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" wrote:

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

On Fri, 7 Mar 2008 19:22:00 -0800, FeFi wrote:

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? ? ? ? ? ? ?

"Jim May" wrote:

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" wrote:

I use DataFilterAutoFilter 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/comm...lic.excel.misc