Excel

Highlight the columns being used to AutoFilter a range

Ease of Use

Easy

Version tested with

97, 2000, 2002, 2003 

Submitted by:

byundt

Description:

As user picks one or more columns to AutoFilter some data, those columns will be highlighted. Turning AutoFilter off for those columns will unhighlight them. 

Discussion:

If you are using the AutoFilter tool to filter data, it may be desirable to print the results and show (by highlight color) which columns were used in filtering. This will also serve as a reminder to the user if data manipulation is complex. 

Code:

instructions for use 

			
 'This sub goes in code pane for the worksheet containing AutoFilter data
Private Sub Worksheet_Calculate() 
    ColorAutoFilter 
End Sub 
 
 'This sub goes in a regular module sheet
Sub ColorAutoFilter() 
     'This sub should be triggered by the recalculation of the worksheet and then called by a
     'Worksheet_Calculate sub. You can force a recalculation after an AutoFilter by using the "trick"
     'of including at least one volatile cell in your worksheet. A volatile cell has a formula using
     'volatile functions like NOW, TODAY, OFFSET, INDIRECT, RAND, INFO or CELL (except when CELL
     'uses the "filename" parameter).
     'If more than one worksheet uses AutoFilter, then each sheet's Worksheet_Calculate sub may call ColorAutoFilter
    Dim FilterNum As Long 
    With ActiveSheet 
        If .AutoFilterMode Then 
            For FilterNum = 1 To .AutoFilter.Filters.Count 
                If .AutoFilter.Filters(FilterNum).On Then 
                    .AutoFilter.Range.Columns(FilterNum).Interior.ColorIndex = 6 'yellow
                Else 
                    .AutoFilter.Range.Columns(FilterNum).Interior.ColorIndex = xlNone 
                End If 
            Next 
        Else 
            .Cells.Interior.ColorIndex = xlNone 
        End If 
    End With 
End Sub

How to use:

  1. Right-click the sheet tab of the worksheet containing the AutoFilter data.
  2. Choose "View Code" from the resulting pop-up.
  3. Paste the first sub (Worksheet_Calculate) in the resulting module sheet.
  4. Use the Insert...Module menu item to create a blank regular module sheet.
  5. Paste the ColorAutoFilter sub in this regular module sheet.
  6. Repeat steps 1-3 for any other worksheets containing AutoFilter data.
  7. Press Alt + Q to close the VBE.
  8. At least one cell on the worksheet containing the AutoFilter must be volatile?see discussion in "Test the code" below.
  9. Save workbook before any other changes.
 

Test the code:

  1. This code is triggered by the recalculation of any cell on the worksheet. You can force a recalculation after performing an AutoFilter by the "trick" of including at least one volatile cell in your worksheet?such as cell I1 in the sample workbook. A volatile cell has a formula using volatile functions like NOW, TODAY, OFFSET, INDIRECT, RAND, INFO or CELL (except when CELL uses the "filename" parameter).
  2. Make sure that your data has header labels above each column.
  3. Select the data to be AutoFiltered, including the header labels.
  4. Open the Data...Filter...AutoFilter menu item to turn the AutoFilter on.
  5. Click the arrow at right of one of the header labels to apply a filter. That column should then become highlighted yellow.
  6. Click the arrow at right of another header label and apply a filter to that column too. Now two columns should be highlighted yellow.
  7. Click arrow on the first column header label and choose (All). That column will now become unhighlighted.
 

Sample File:

HighlightAutoFilterColumn.zip 8.09KB 

Approved by mdmackillop


This entry has been viewed 164 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express