The following step-by-step example shows how to filter rows by multiple colors in Excel.
Let’s jump in!
Step 1: Enter the Data
First, let’s enter the following list of tasks in Excel that are color-coded based on whether they have been completed or not:
Step 2: Use VBA to Extract Color Code
Next, we will write a simple function in VBA to extract the color from each cell as an integer value.
To do so, click Alt + F11 to open the VB Editor. Then click the Insert tab and then click Module.
In the module window that appear, type the following code:
Function FindColor(CellColor As Range) FindColor = CellColor.Interior.ColorIndex End Function
This will create a custom function that we can use in Excel to extract the background color of any cell as an integer value.
The following screenshot shows how to do so in practice:
Once you’ve entered the code, feel free to close out of the VB Editor. The code will automatically be saved.
Step 3: Extract Colors from Cells
Next, let’s type the following formula into cell B2 to extract the background color from cell A2:
=FindColor(A2)
Then click and drag this formula down to each remaining cell in column B:
Column B now shows the background color (as an integer value) of each corresponding cell in column A.
Step 4: Filter by Multiple Colors
Now that we have the background color of each cell as an integer value, we can simply filter the rows based on the values in the Color column.
To do so, highlight the cell range A1:B11, then click the Data tab along the top ribbon, then click the Filter icon within the Sort & Filter group.
Then click the dropdown arrow next to Color and uncheck the box next to 35:
Once you click OK, the rows will be filtered to only show the ones where the color of the task is either yellow or red:
Feel free to uncheck whichever color codes you’d like to filter by a different set of colors.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Use Wildcard in FILTER Function
Excel: How to Filter Cells that Contain Multiple Words
Excel: How to Count Filtered Rows
This works perfectly.
But how to make it dynamic, example if I change the color of the cell then to get the correct value I have to again go to the formula edit and hit enter only then the correct value is displayed
Hi Brijesh…In Excel, formulas do not automatically recalculate based on cell color changes because Excel formulas cannot directly detect cell color. To dynamically update the value based on cell color changes, you would need to use VBA (Visual Basic for Applications). Here’s how you can set this up:
### Step-by-Step: Create a VBA Function to Dynamically Filter by Color
1. **Open VBA Editor:**
– Press `Alt + F11` to open the VBA editor.
2. **Insert a New Module:**
– In the editor, go to `Insert` → `Module`.
3. **Add the VBA Code:**
Copy and paste the following code into the module:
“`vba
Function CountByColor(rng As Range, color As Range) As Long
Dim cell As Range
Dim count As Long
count = 0
For Each cell In rng
If cell.Interior.Color = color.Interior.Color Then
count = count + 1
End If
Next cell
CountByColor = count
End Function
“`
4. **Close the VBA Editor:**
– Press `Alt + Q` to close the editor and return to Excel.
5. **Use the Function:**
– Now you can use the formula `=CountByColor(rng, color)` in Excel, where:
– `rng` is the range of cells you want to count.
– `color` is a reference to a cell with the color you want to filter by.
Example:
“`excel
=CountByColor(A1:A10, B1)
“`
This will count how many cells in the range `A1:A10` have the same background color as `B1`.
### Dynamic Behavior:
Whenever you change the color of any cell in the `rng`, the formula will automatically update the count without needing to edit the formula and press Enter again.
Let me know if you need further clarification or assistance!