Prev: Automatic Excel data selection and transfer/copy to another wo
Next: Get cell value
From: OssieMac on 30 Mar 2010 09:03
Hello Patrice,

I don't believe you can change the color of the dropdowns.

You could use the following Worksheet Calculate event to change the
background color of the column headers on the AutoFilter Range. However,
somewhere out to the right of your AutoFiltered data insert the following
formula in row 1. This is to force a worksheet calculate event to run each
time the Autofilter is changed. If you already have subtotals or other
calculations on the worksheet that will be calculated when the AutoFilter is
changed that this is not required.

=SUBTOTAL(3,A:A)

The A:A in the formula can be any column within the AutoFiltered range.

The following code goes in the worksheet module. Not sure if you know how to
do this but just in case here are instructions to insert the code.

Right click the worksheet tab.
Select View Code.
The VBA Editor will open in the worksheet module.
Copy the below code and paste it into the VBA editor. (Do not change Sub
name.)
Click the X with the red background top right of editor window to close the
VBA editor.
You will need macros enabled to run with notification. (See Help for how to
do this.)
If xl2007 then Save as Macro enabled workbook. (Earlier versions save as
normal)


Private Sub Worksheet_Calculate()
Dim rngFilter As Range
Dim i As Long

With Me
If .AutoFilterMode Then
With .AutoFilter.Range
Set rngFilter = _
.Resize(1, .Columns.Count)
End With
With .AutoFilter
For i = 1 To .Filters.Count
With .Filters(i)
If .On Then
rngFilter.Cells(1, i) _
.Interior.ColorIndex = 4 'Green
Else
rngFilter.Cells(1, i) _
.Interior.ColorIndex = xlNone
End If
End With
Next i
End With
End If
End With

End Sub



--
Regards,

OssieMac


"Patrice Boulanger" wrote:

> Is it possible to change the arrow color for an active filter under
> Autofilter in Excel?
>
> It is almost impossible to distinct the color blue to black on the scroll
> down autofilter button on the first row of a spreadsheet.
>
> If this is not possible, I suggest to add a customizing function for this.
>
> I feel like I'm color blind!
>
> Best regards
>
> Patrice Boulanger
 | 
Pages: 1
Prev: Automatic Excel data selection and transfer/copy to another wo
Next: Get cell value