Default colours for filter arrows in Excel

Topic

New Reply

Is there any way to change the default colours for the filter arrows in Excel 2002?

Unselected is black and selected is blue – but in a large spreadsheet (30+columns) it is very difficult to see which columns have been filtered. It would be much better if they could be defaulted to say red or yellow.

I would also love to be able to change the default fill colour on the toolbar button from yellow to green!

Reply | Quote

Replies

Unfortunatley they are hard-coded….

BUT, you may find this technique for highlighting the columns useful: http://lounge.windowssecrets.com/index.php?showtopic=441831&view=findpost&p=441861

Steve

Reply | Quote

Ecellent! Many thanks.

A side effect is that I can use the formula SUBTOTAL(9,A:A) to count the rows displayed by added a hidden column withe 1s in it and pointing the formula to that column – unless there is a more elegant solution?

Another related question. The sheet that I am filtering is populated temporarily with data gathered from other sheets by macros. Is there a line that I could add to the start of those macros to clear any filters that are set?

Reply | Quote

You could use:

Code:

   With ActiveSheet
      If .AutoFilterMode Then .AutoFilter.Range.AutoFilter
   End With

to actually remove the autofilter completely, or:

Code:

   With ActiveSheet
      If .AutoFilterMode Then .ShowAllData
   End With

to just have all data visible, but leave the autofilter set up.

Reply | Quote

Rory

Many thanks for all this – it is working perfectly.

I answered my own question on counting the rows – I used =SUBTOTAL(3,A8:A1000) instead of =SUBTOTAL(9,A:A) to count occupied rows below my headers.

Best wishes

Peter

Reply | Quote

I spoke too soon!

.showalldata will not work when I protect the sheet.

Reply | Quote

Have you allowed filtering?

You could always just add code to unprotect the sheet, clear the filter, then reprotect.

Reply | Quote

Hi Rory

Yes – that is what I thought. Works fine if a filter is set when I run the macro, but fails if not.
(Runtime error ‘1004’:
ShowAllData method of Worksheet Class failed)

Where am I going wrong?

Here is the code:

Worksheets(“Stud Book”).Unprotect
With ActiveSheet
If .AutoFilterMode Then .ShowAllData
End With
Worksheets(“Stud Book”).Protect Contents:=True, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True

I want to protect the sheet allowing:
Select unlocked cells
Format cells
Sort
Use Autofilter

Best wishes

Peter

Reply | Quote

Two things need changing – you need to specify the same worksheet, and you actually need to test if a filter is in use:

Code:

With Worksheets("Stud Book")
   .Unprotect
   If .AutoFilterMode Then
      If .FilterMode then .ShowAllData
    End If
   .Protect Contents:=True, AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
End With

Reply | Quote

Hi Rory

Once again I am greatly indebted to you for your help.

I had thought of simply removing the autofilter and then replacing it, but your solution is much simpler.

I had also added a line to reset the fill colour, but that seems unnecessary with your solution.

Best wishes

Peter

Reply | Quote

I have another question.

On the same worksheet, I wish to change the format of a range of cells depending on which macro I run to populate the sheet. So I wish to add a line to the macro (where it has unprotected the sheet) that says:

1 Worksheets(“Stud Book”).Range(“AH8:AH1000”) change the format to whole number (no decimal places)

2 Worksheets(“Stud Book”).Range(“AH8:AH1000”) change the format to allow a four digit number which may have a leading zero.

Reply | Quote

You would need to set the NumberFormat property to either “#,##0;-#,##0;0” or “0000” respectively.

Reply | Quote