Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I change the colour of the 'filter arrows'
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 |
#2
|
|||
|
|||
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 FormatConditional Formatting From the first dropdown, choose Formula Is In the formula box, type: =COUNTA($A:$A)<SUBTOTAL(3,$A:$A) Click the Format button On the Patterns tab, select a colour Click OK, click OK sandro wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
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 "Debra Dalgleish" wrote: 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 FormatConditional Formatting From the first dropdown, choose Formula Is In the formula box, type: =COUNTA($A:$A)<SUBTOTAL(3,$A:$A) Click the Format button On the Patterns tab, select a colour Click OK, click OK sandro wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
|
|||
|
|||
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. rontl wrote: 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 "Debra Dalgleish" wrote: 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 FormatConditional Formatting From the first dropdown, choose Formula Is In the formula box, type: =COUNTA($A:$A)<SUBTOTAL(3,$A:$A) Click the Format button On the Patterns tab, select a colour Click OK, click OK sandro wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I change the colour of the 'filter arrows'
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? -- TommyB "Debra Dalgleish" wrote: 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. rontl wrote: 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 "Debra Dalgleish" wrote: 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 FormatConditional Formatting From the first dropdown, choose Formula Is In the formula box, type: =COUNTA($A:$A)<SUBTOTAL(3,$A:$A) Click the Format button On the Patterns tab, select a colour Click OK, click OK sandro wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I change the colour of the 'filter arrows'
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 -- Regards Roger Govier "TommyB" wrote in message ... 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? -- TommyB "Debra Dalgleish" wrote: 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. rontl wrote: 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 "Debra Dalgleish" wrote: 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 FormatConditional Formatting From the first dropdown, choose Formula Is In the formula box, type: =COUNTA($A:$A)<SUBTOTAL(3,$A:$A) Click the Format button On the Patterns tab, select a colour Click OK, click OK sandro wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the color of Filter Arrows in Excel to BRIGHT RED | Excel Discussion (Misc queries) | |||
Excel List range, filter arrows disappeared | Excel Discussion (Misc queries) | |||
Is it possible to change the blue colour of an activated Auto Filter button? | Excel Discussion (Misc queries) | |||
Filter arrow colour in Excel | Excel Worksheet Functions | |||
Macro button colour change??? | Excel Worksheet Functions |