How do I change the colour of the 'filter arrows'

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I use filters in excel extensively, occasionally I do not realise I have a
filter on a worksheet as the dark blue filter arrows are not destinctive
enough to the black arrows when the filters are off. How do I change the
arrows to a brighter colour e.g red
 
There's no way to change the colour of the filter arrows.

If you can use programming, there's a sample file here in which the
heading cell is coloured if a filter is applied to that field:

http://www.contextures.com/excelfiles.html

Under Filters, look for 'Colour Filter Headings'

Or, without programming, you can colour all the headings if any column
in the table has been filtered. For example:

Select all the headings
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type:
=COUNTA($A:$A)<>SUBTOTAL(3,$A:$A)
Click the Format button
 
I downloaded the file, and it works great inside the sample. But when I take
a filtered file i already have, and create a macro module in it, and paste
your code into it, the coloring only works if I have your workbook open. It
works very nicely, but it only works when your workbook is open. I have
AutoFilter turned on, and I inserted "=TODAY()" into a cell to force
recalculation, but still no luck.

Help!
Thanks,
Ron
 
The code should be pasted onto the worksheet module for the sheet that
contains the autofilter. Right-click on the sheet tab, choose View Code,
and paste the code where the cursor is flashing.
 
Debra...this works great for me as long as my worksheet is unprotected. When
I protect my worksheet and then attempt to filter a column I get a VB
run-time error 1004, "unable to set the colorindex property of the Interior
class."
Can you help?
 
Hi Tommy

You just need to amend Debra's code to take the protection off at the
beginning, and re-instate at the end
Change the password to match what you have used.

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

If ActiveSheet.AutoFilterMode Then
ActiveSheet.Unprotect Password:="secret"

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
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFiltering:=True, Password:="secret"

End Sub
 
Back
Top