Bottom line: Learn how to apply color and icon filters with VBA.  Includes examples for filtering for font color, fill color, and conditional formatting icons.
Skill level: Intermediate

VBA AutoFilter Automate Date Filters

Download the File

The Excel file that contains the code can be downloaded below.  This file contains code for filtering different data types and filter types.  Please see my article on The Ultimate Guide to AutoFilters in VBA for more details.

VBA AutoFilters Guide.xlsm (100.5 KB)

Color & Icon Filters in Excel

Filters for font and fill colors were introduced in Excel 2010.  This allows us to quickly filter columns based on cell formatting that can be applied manually or by conditional formatting.

The Filter by Color menu appears when a column contains any font or fill colors.  It also appears when the column contains icons created by conditional formatting.  We can use VBA to apply these filters as well.

Let's take a look at some code examples for the different color filters.

Color & Icon Filters in VBA

Sub AutoFilter_Color_Icon_Examples()
'Examples for filtering columns with COLORS and ICONS
 
Dim lo As ListObject
Dim iCol As Long

  'Set reference to the first Table on the sheet
  Set lo = Sheet1.ListObjects(1)
  
  'Set filter field
  iCol = lo.ListColumns("Product").Index
  
  'Clear Filters
  lo.AutoFilter.ShowAllData
  
  With lo.Range
  
    'Colors

    'Font and fill colors are set in Criteria 1.
    'The macro recorder gives us the RGB value.  RGB can also
    'be found in the More Colors menu on the Custom tab.
  
    'Cell Fill Color for dark red
    .AutoFilter Field:=iCol, _
                Criteria1:=RGB(192, 0, 0), _
                Operator:=xlFilterCellColor
      
    'Font Color for dark green
    .AutoFilter Field:=iCol, _
                Criteria1:=RGB(0, 97, 0), _
                Operator:=xlFilterFontColor
    
    
    'Icons
  
    'Set filter field
    iCol = lo.ListColumns("Icon").Index
          
    'Clear Filters
    lo.AutoFilter.ShowAllData
  
    'Icon (conditional formatting)
    .AutoFilter Field:=iCol, _
                Criteria1:=ThisWorkbook.IconSets(xl4CRV).Item(4), _
                Operator:=xlFilterIcon
        
    End With
    
End Sub

Filters & Data Types

The filter drop-down menu options change based on what type of data is in the column.   We have different filters for text, numbers, dates, and colors.  This creates A LOT of different combinations of Operators and Criteria for each type of filter.

I created separate posts for each of these filter types.  The posts contain explanations and VBA code examples.

The file in the downloads section above contains all of these code samples in one place.  You can add it to your Personal Macro Workbook and use the macros in your projects.

Please leave a comment below with any questions or suggestions.  Thanks! 🙂

3 comments

Your email address will not be published. Required fields are marked *

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly