Filter by color

How to filter by color using Excel and VBA methods

METHOD 1. Filter by color

EXCEL

Select data > Home tab > Sort & Filter > Filter > Click on filter drop down button > Click on Filter by Color > Select color

1. Select the range that captures the colored cell that you want to filter, including headers
Note: in this example we are selecting range (B2:C9).

2. Select the Home tab.

3. Click on Sort & Filter in the Editing group.
4. Click on Filter.

Click on Sort & Filter and click on Filter

5. The top row of the selected data will now have drop down buttons from which you can apply your filter.

6. Click on the filter drop down button.
7. Click on Filter by Color.
8. Select the color that you want to filter for.
Note: in this example we are filtering for a green color in the 'Match Outcome' column.

Select filter dropdown button click on Filter by Color and select color

9. This image represents the results of the filtered data that filters for a green color in the 'Match Outcomes' column.

METHOD 1. Filter by color using VBA

VBA

Sub Filter_by_color()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

ws.Range("B2:C9").AutoFilter field:=2, Criteria1:=RGB(41, 247, 110), Operator:=xlFilterCellColor

End Sub

PREREQUISITES
Worksheet Names: Have a worksheet named Sheet1.
Color Range: In this example the data that is being filtered by color is captured in range ("B2:C9"). Therefore, if using the exact same VBA code, the VBA code will apply a filter to this range.
Filter Field: In this example we are filtering against the second column ('Match Outcome') by assigning a value of 2 against 'filed', in the VBA code.

ADJUSTABLE PARAMETERS
Worksheet Names: Select the worksheet that captures the data/color that you want to filter by changing the Sheet1 worksheet name.
Color Range: Select the range that captures the color that you want to filter by changing the range reference ("B2:C9").
Filter Field: Select the filter filed by changing the filed number (2) in the VBA code to any number that is withing the applied filter.
Filter Criteria Color: Select the filter criteria by changing the criteria color (RGB(41, 247, 110)) in the VBA code to any color that exists in the filtered range.

METHOD 2. Filter by color with cell reference using VBA

VBA

Sub Filter_by_color()

'declare a variable
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

ws.Range("B2:C9").AutoFilter field:=(ws.Range("C2").Column - ws.Range("B2").Column) + 1, Criteria1:=RGB(41, 247, 110), Operator:=xlFilterCellColor

End Sub

PREREQUISITES
Worksheet Names: Have a worksheet named Sheet1.
Color Range: In this example the data that is being filtered by color is captured in range ("B2:C9"). Therefore, if using the exact same VBA code, the VBA code will apply a filter to this range.
Filter Field: In this example we are filtering against the second column ('Match Outcome') by applying a calculation where we take the column number of the filed that we want to filter by and subtract the column number of the first column where we have applied a filter and add a value of 1.

ADJUSTABLE PARAMETERS
Worksheet Names: Select the worksheet that captures the data/color that you want to filter by changing the Sheet1 worksheet name.
Color Range: Select the range that captures the color that you want filter by changing the range reference ("B2:C9").
Filter Field: Select the filter filed by changing cell references that relate to the field that you want to apply the filter and the first column of the filtered data. In this example, the field that we are filtering against in the second field, with the heading captured in cell ("C2")./span>
Filter Criteria: Select the filter criteria by changing the criteria color (RGB(41, 247, 110)) in the VBA code to any color that exists in the filtered range.

Explanation about how to filter by color

EXPLANATION

EXPLANATION
This tutorial explains and provides step by step instructions on how to filter by color using Excel and VBA methods.

Excel Method: This tutorial provides one Excel method that can be applied to filter by color. You initially add the filter option to the selected data and then apply the relevant filter. Using this method you can filter by color in eight steps.

VBA Methods: This tutorial provides two VBA methods that can be applied to filter by color. The first method applies the filter filed and criteria directly into the VBA code. The second method applies a more dynamic approach where it references to the relevant cells to calculate the filter field.