Cell colour when filtering

[Tefleon]

Hi Experts.

This could be standard functionality that I'm missing rather than VBA or some wonderful macro and therefore I apologise in advance if I've missed something.

I have a worksheet...
In row 1 I have my column headers.
In row 1 I have column filters. The standard ones in Excel 2007: data > filters
In the other rows I have my data.

The filtering works as I expect and fine for what I need.

What I'm after is a way to change the background colour of any heading where I've applied the filter.

Example.

Cell A1.
This is a name column where I can filter by first name. If I've applied the filter to this column then cell A1 should format red. When I cancel the filter it should return it the previous format. The formatting of the column data should not be affected.

I can and do filter multiple columns for the same query so would need to be able to see which on my columns are filtered at any time.

Is this standard functionality and if not, how do I create it??

Thanks!

 

[Andrew Poulsom]

You can use this custom function:

Function FilterOn(Rng As Range) As Boolean
    Dim x As Integer
    Application.Volatile True
    Set Rng = Rng.Cells(1, 1)
    If Rng.Parent.AutoFilterMode = False Then
        FilterOn = False
        Exit Function
    End If
    With Rng.Parent.AutoFilter
    If Application.Intersect(Rng, .Range) Is Nothing Then
        FilterOn = False
        Exit Function
    End If
    x = Rng.Column - .Range.Cells(1, 1).Column + 1
    FilterOn = .Filters(x).On
    End With
End Function

In conditional formatting formula is:

=FilterOn(A1)

 

[Tefleon]

Works perfectly

Thanks