Changing the color of Autofilters

[Landcruiser87]

Ok, This might be an extreme shot in the dark, but does anyone know of a way to change the color of the auto filters from that extremely dark blue to something that, ohhh i don't know, might actually stand out in a spreadsheet of many filtered items! The problem is when i'm looking for whats been filtered on the sheet and instead of just removing the filter entirely and starting over, i'd like to see what the hell is going on.

I've searched high and low and found no solution as of yet.

So its to the message board with this quandry.

Thanks for your help

 

[yytsunamiyy]

Well, I developed a UDF for that problem:

Excel Workbook
	A	B	C
1	TRUE	FALSE	FALSE
2	Data1	Data2	Data3
3	1	2	5
4	1	2	5
5	1	2	5
6	1	2	5
7	1	2	5
8	2	3	6
9	2	3	6
Sheet

Function FilterCrit(rng As Range) As Boolean

Dim Col As Integer

Col = rng.Column

FilterCrit = ActiveSheet.AutoFilter.Filters(Col).On


End Function

as you can see, the UDF returns True / false Statements.

With a little conditional formatting you can easely mark the filtered columns with a color of your choice.

 

[Andrew Poulsom]

I don't think so, but here is a custom function you could use in Conditional Formatting:

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 CF choose Formula Is and enter a formula like:

=FilterOn(A1)

 

[Landcruiser87]

Well i couldn't get either of them to change the little buggers with VBA. But thanks for the replies! I did find an application for your code Andrew, thanks again!