i dont think there is a way to change the colour of the drop down arrow
there is a way to make the arrow visible / invisible. and there is als
a function that would colour that cell (column header) for whic
autofilter is on.
this is from excel help, to make the dropdown arrow invisible. you ca
use this to hide all the column headers that currently do not hav
autofilter on.
Worksheets("Sheet1").Range("A1").AutoFilter _
field:=1, _
Criteria1:="Otis"
VisibleDropDown:=False
the following function, in conjunction with conditional formatting
will colour that column header cell for which autofilter is currentl
on (a criterion has been set).
Public Function CheckFilter(rngCell As Range)
Dim lngClmOfst As Long 'variable to hold column offset fo
the whole filter range
Dim rngFilter As Range 'variable to hold the whole filte
range
Dim ws As Worksheet
Set ws = rngCell.Parent 'deriving the sheet to which th
cell belongs
On Error Resume Next
If ws.FilterMode = False Then Exit Function 'if the whole sheet i
not in filtermode
'set the autofilter range into the variable
Set rngFilter = ws.AutoFilter.Range
'check if the activecell falls within the filter range
If Intersect(rngCell.EntireColumn, rngFilter) Is Nothing Then Exi
Function
'derive the column offset of the activecell for the filter range
lngClmOfst = rngCell.Column - rngFilter.Columns(1).Column + 1
If ws.AutoFilter.Filters(lngClmOfst).On Then
CheckFilter = "Y"
End If
End Function
select the column header. go to conditional formatting. select Formul
Is. enter: =CHECKFILTER(A1)="Y" - and select a colour