VBA AutoFilter by Color: Filter Fill and Font Colors

Yes. In Excel VBA, use the AutoFilter method with Operator:=xlFilterCellColor to filter by a cell’s fill color, or Operator:=xlFilterFontColor to filter by its font color. The color criterion is a color value, such as one returned by Interior.Color or Font.Color.

How AutoFilter by Color Works

An AutoFilter color filter shows rows whose cells in a selected field match the requested color. It hides the other rows; it does not change cell formatting, delete records, or rearrange the data. You can still clear the filter later to show all the rows again.

The field is the column being checked, and the color criterion says which color to match. In VBA, the field number is relative to the left edge of the range you filter, not the worksheet’s column number. For example, if the filter range is A1:D100, Field:=3 means column C. If the range starts in column B, its third field is column D.

For the color argument, VBA accepts an Excel color value. A common way to create one is RGB(255, 255, 0), which represents yellow. Getting the color from a sample cell is often more dependable because it avoids guessing the exact RGB value used in the workbook.

Example: Filter Rows by Fill Color

This Windows example assumes the worksheet is named Data, row 1 contains headers, the data range is A1:D100, and the fill color to match is present in C2. Since the range starts in column A, its third field is column C. Change the sheet name, range, and field number to fit your data.

Sub FilterRowsByFillColor()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim colorToFind As Long

    ' Use the worksheet and a cell containing the target fill color.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")
    colorToFind = ws.Range("C2").Interior.Color

    ' Filter the third field by that cell's fill color.
    dataRange.AutoFilter Field:=3, _
        Criteria1:=colorToFind, Operator:=xlFilterCellColor
End Sub

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, paste the procedure, and run it while the workbook is open. On Mac, the Visual Basic Editor shortcut is usually Option+F11 or Fn+Option+F11, depending on the keyboard. The standard Range.AutoFilter method has a documented Mac limitation, so do not assume this range-based macro will work there; use Excel’s filter menus or verify a table-specific VBA approach in the Mac version you use.

Choosing the Right Field and Color

Two details account for many failed color filters: selecting the wrong field and supplying a color value that is not the one Excel uses for the target cells. Confirm both before changing the macro.

Check the range boundary. The top row of the AutoFilter range should contain the headers. If the range is B1:F100, then Field:=3 filters column D, not column C.
Check the sample cell. The example reads the fill color from C2. If C2 is not formatted with the color you want, choose another sample cell in the same field.
Check the kind of color. A fill-color filter and a font-color filter are different operations. A colored font does not count as a colored cell fill.
Check the data range. Include all rows to be filtered and the header row, but avoid including unrelated notes or a second table.

For a fixed yellow fill rather than a sample cell, set colorToFind = RGB(255, 255, 0) in the macro. This is useful when the intended color is known and consistent. For theme colors or shades adjusted with tint, reading the color from a correctly formatted sample cell can be easier than trying to reproduce the color manually.

Filtering by Font Color Instead

If the text itself is colored, use the font-color operator and read the font color from a sample cell. The following procedure assumes the same Data sheet and range as the fill example, with the target font color in C2.

Sub FilterRowsByFontColor()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim colorToFind As Long

    ' Read the target text color from a sample cell.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")
    colorToFind = ws.Range("C2").Font.Color

    ' Filter the third field by font color.
    dataRange.AutoFilter Field:=3, _
        Criteria1:=colorToFind, Operator:=xlFilterFontColor
End Sub

Choose one operator that matches the formatting you want to inspect. If some cells have a yellow fill and others have red text, filtering on cell color will not find the red font just because it looks visually prominent. Conversely, the font-color operator does not match a fill. A color filter on a field selects a color criterion for that field; it is not a general search for a color anywhere across the row.

Using Color Filters with Excel Tables

Excel tables already have header filter buttons, which makes them convenient for repeated filtering. Select a cell in the table and use the dropdown in the relevant column, then choose Filter by Color and the fill or font color. The precise options shown depend on the formatting available in that column.

For VBA, make sure the range or table field you target corresponds to the column whose cells carry the color. A table’s column position is also relative to the table, not to the worksheet. If a table begins in column C, its first field is column C even though that is worksheet column 3. If table columns are added or moved, review hard-coded field numbers to ensure the macro still targets the intended column.

Tables automatically expand when data is added in the usual way, while a hard-coded range such as A1:D100 has a fixed boundary. If new rows may be added, update the range or use a table-based design so the filter covers the full dataset. Avoid overlapping filters or treating two separate data blocks as one range.

Conditional Formatting and Displayed Colors

A cell can look colored because of a manually applied fill, a theme color, or a conditional-formatting rule. Those are not always equally straightforward to inspect in VBA. The example’s Interior.Color reads the cell’s ordinary fill property; it is not a general-purpose way to identify every color a conditional-formatting rule displays. Similarly, Font.Color reads the ordinary font color.

Excel’s interface can offer color filtering for colors associated with conditional formatting, but a VBA macro that reads a sample cell’s ordinary formatting may retrieve a different value from the visible result. If your colors come from rules, first test the desired filter in the column’s dropdown using Filter by Color. If that succeeds, verify the VBA criterion against a known cell and the exact Excel version in use rather than assuming the sample-cell code captures the displayed conditional color.

For a color-based workflow that must be predictable, consider adding a separate helper column with a value such as “Review” or “Complete” based on the underlying data. Filtering on a text label or status value is generally easier to maintain than relying on formatting to represent information.

Clearing Filters and Working with Existing Criteria

Color filters can coexist with filters on other columns. For example, filtering a Status column to “Open” and a fill-color column to yellow displays rows that meet both criteria. Applying a new color criterion to a field replaces that field’s current filter criterion, while criteria on other fields can remain in effect.

To clear a color filter using the interface, open that column’s dropdown and choose the command to clear the filter from that field. To show all rows after filtering through VBA, check whether the worksheet is filtered before calling ShowAllData. This clears active filter criteria on the sheet, including criteria in other columns, so it is broader than clearing just the color condition.

Sub ShowAllFilteredRows()
    Dim ws As Worksheet

    ' Change the sheet name to the worksheet being filtered.
    Set ws = ThisWorkbook.Worksheets("Data")

    ' Clear active criteria without removing the filter buttons.
    If ws.FilterMode Then ws.ShowAllData
End Sub

FilterMode indicates whether rows are currently filtered, while AutoFilterMode indicates whether AutoFilter arrows are displayed. They are not interchangeable: a sheet can have filter buttons without currently hiding rows. This distinction helps avoid calling ShowAllData when there is no active filter.

Common Problems and Practical Checks

Run-time error or no results: Confirm that the range includes headers and that the field number is between 1 and the number of columns in the range.
The wrong rows appear: Verify the sample cell and the target field. A sample cell from a different column or a different shade can produce a valid filter that simply does not match what you intended.
The macro filters only part of the list: Check that the range includes the full dataset. A fixed address such as A1:D100 will not automatically include row 101.
Rows remain hidden after changing the filter: Another field may still have an active criterion, or rows may be hidden manually. Clear the other filters and check for manually hidden rows before concluding the color filter failed.
The code works on Windows but not Mac: The range method has a documented Mac limitation. Use the Excel interface on Mac or adapt and verify the automation for the workbook’s table and Mac Excel version.

Also distinguish a filter from a sort. Filtering temporarily hides nonmatching rows; sorting changes their order. A color filter does not sort the matching rows to the top, and it does not create a permanent list of matching records. If you need a separate extract, copy the visible results to another location after filtering, taking care to copy visible cells rather than the entire hidden range.

When a Helper Column Is a Better Alternative

Color is often a visual cue rather than the underlying data. If yellow means “needs review,” a helper column containing that status is usually more reliable than filtering for yellow. It gives the meaning a searchable value, makes the workbook easier to understand for people who use different color settings, and avoids ambiguity when shades or formatting rules change.

A helper column is especially useful when several colors mean different things, when the color is produced by conditional formatting, or when the workbook is refreshed or shared frequently. You can still keep the color as a visual aid while filtering by the status text. Use a color filter when the color itself is the required criterion—for example, when auditing manually highlighted cells—and use a data field when the color only communicates a category.

Frequently Asked Questions

Can VBA AutoFilter by cell color?
Yes. Use Operator:=xlFilterCellColor and set Criteria1 to a color value, such as a value read from a cell’s Interior.Color.

How do I filter by font color in VBA?
Use Operator:=xlFilterFontColor and supply the desired font color as Criteria1. A sample cell’s Font.Color can provide that value for ordinary font formatting.

Does Field refer to an Excel column letter?
No. It is a number relative to the left edge of the range passed to AutoFilter. In A1:D100, Field 3 is column C; in B1:F100, Field 3 is column D.

Can I filter by a color from conditional formatting?
The Excel dropdown may offer color filtering for conditional-formatting results, but reading Interior.Color from a cell does not necessarily return its displayed conditional fill. Test the interface first and verify any VBA color criterion against the specific rule and version.

Does a color filter delete or recolor rows?
No. It hides rows that do not match. Clear the filter to display them again; the filter does not edit the stored cell values or formatting.

Why does the macro return no matching rows?
Check that the range and field are correct, that the chosen sample cell has the intended color, and that you chose the fill or font operator that matches the formatting. Existing filters on other fields may also narrow the results.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.