Format a Filtered range's header?

[0000_AAAA_0000]

Hi, and many thanks in advance!


Say I have a range "range("A1").currentregion named Rng


say Rng is filtered


is it posible to change the formatting of Rng'headers if the filter i
ON

eg: Rng.AutoFilter Field:=3, Criteria1:="Europe"

I mean, if A1=Country B1=Population C1=Continent

If I chose in C1 the filter criteria Europe, then C1 must be Yellow
then if i say in A1=Spain A1 must be yellow


then if i take out the filters A1:C1 must be without colour.
I mean:
Rng.AutoFilter Field:=3
Rng.AutoFilter Field:=2
Rng.AutoFilter Field:=1

I am trying to do this because the blue colour showed in the dropdow
control when pressed is not enought.
so I would like to make it ckear if a filter is benn Pressed!

Any idea????????

 

[Tom Ogilvy]

Private Sub Worksheet_Calculate()
Dim f As Filter
If Me.AutoFilterMode = True Then
i = 0
For Each f In Me.AutoFilter.Filters
i = i + 1
If f.On Then
Cells(1, i).Interior.ColorIndex = 6
Else
Cells(1, i).Interior.ColorIndex = xlNone
End If
Next
Else
Rows(1).Interior.ColorIndex = xlNone
End If
End Sub

right click on the sheet tab and select view code. Place this code in that
module.

This only works if changing the filter causes a calculate. You can force
this by having at least one formula refer to a cell within the filter range.
A good formula might be to put this to the right of the header of the last
column in the filter range.

=SubTotal(3,$A2:$A30)

or make it

="Visible Rows: " & SubTotal(3,$A$2:$A$30)

Where the range $A$2:$A$30 would span the filter range.