Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Filter (Excel 2000/2003)

    Is there a way to change the default colour of an auto filter arrow button.
    e.g when the filter is applied the filter arrow changes from black to dark blue. In a spreadsheet with a number of columns it is difficult to see the columns on which a filter is applied. Can you change the dark blue to say red?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Auto Filter (Excel 2000/2003)

    No, as far as I know, the color is hard-coded into Excel. Sorry.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Filter (Excel 2000/2003)

    A workaround would be to highlight the columns. This routine will Color the autofiltered columns headings yellow and clear the unfiltered ones.

    Step 1:Add this code to a module

    <pre>Option Explicit
    Sub ColorDisplayFilter()
    Dim flt As Filter
    Dim intCol As Integer
    Dim lRow As Long
    intCol = 0
    lRow = ActiveSheet.AutoFilter.Range.Row
    Application.EnableEvents = False
    For Each flt In ActiveSheet.AutoFilter.Filters
    intCol = intCol + 1
    If flt.On Then
    Cells(lRow, intCol).Interior.Color = vbYellow
    Else
    Cells(lRow, intCol).Interior.ColorIndex = xlColorIndexNone
    End If
    Next flt
    Application.EnableEvents = True
    End Sub</pre>


    2) Then add this routine to the thisworkbook object
    <pre>Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    If Sh.AutoFilterMode Then ColorDisplayFilter
    End Sub</pre>


    Now whenever the workbook recalculates if there is an autofilter it will run the routine

    3) This is also required To ensure that the calculation event is triggered when changing the filter, on all sheets with an autofilter, add a subtotal formula (eg, and I assume A is a column in the filter)
    <pre>=SUBTOTAL(9,A:A)</pre>


    The formula may be hidden if desired, but must be on the sheet that has the filter to ensure that the sheet triggers the event.

    When all is in place the cells with criteria set will be set to a yellow (change as desired) background.

    Possible enhancement:
    An additional enhancement can be done using a technique by Stephen Bullen and published on John Walkenbach' website to Display AutoFilter criteria

    This technique uses a function to get the criteria and that formula then can be used where desired.The macro I list could also be modified to call the function referenced and put the cells directly in particular cells if desired instead of using formulas in the worksheet. For example adding an optional parameter to allow the criteria to be added (it will be add 1 row above the header row if the header row is not in row 1):
    NOTE: "FilterCriteria" is the function from John Walkenbach's site

    <pre>Sub ColorDisplayFilter(Optional bListCriteria As Boolean = False)
    Dim flt As Filter
    Dim intCol As Integer
    Dim lRow As Long
    intCol = 0
    lRow = ActiveSheet.AutoFilter.Range.Row
    Application.EnableEvents = False
    For Each flt In ActiveSheet.AutoFilter.Filters
    intCol = intCol + 1
    If flt.On Then
    Cells(lRow, intCol).Interior.Color = vbYellow
    Else
    Cells(lRow, intCol).Interior.ColorIndex = xlColorIndexNone
    End If
    If lRow > 1 And bListCriteria Then _
    Cells(lRow - 1, intCol) = " " & FilterCriteria(Cells(lRow, intCol))
    Next flt
    Application.EnableEvents = True
    End Sub</pre>


    And then you can change the line in the calculation event to:
    <pre>If Sh.AutoFilterMode Then ColorDisplayFilter (True)</pre>


    To color the headers and display the criteria in the row above the header.

    Hope this helps,
    Steve

  4. #4
    New Lounger
    Join Date
    May 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Filter (Excel 2000/2003)

    Thank you for your help, I'll have a go. Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •