Excel allows you to make changes in data as per your needs. You can change color coding as well to make your workbook appealing. Using styles and themes in Excel makes your data sheet modern. Your data cells get highlighted when you use the colored feature.
In Excel, you can have a wide range of features and not even one can be used for Excel count colored cells. Definitely, you must not want to use third-party tools for this function, that’s why you need to make your own functions. Most probably, you must be familiar with user-defined functions and that’s what we’ll further discuss.
How to Excel Count Colored Cells with Find and Select Command
Using the Find feature in Excel, you can locate the cells on the bases of a certain format. You can find colored cells as well. Once all the same colored cells are located, you can then count them.
- Open the Home tab and then choose the Find & Select command. Now, click on the Find option.
- Or else you can even use a keyboard shortcut as well; press CTRL + F to open the Find and Replace menu.
- In the Format button, click on the little down arrow and then click on Choose Format From Cell option. As you click on the Format button, it will open up the Find Format menu that lets you choose any combination of formatting.
- This option is effective when you know which the exact color to find.
- Clicking on the little arrow given in the Format menu, you will get options including the format set by selecting a cell.
- Click on the Find All option once the format selection is done. On the bottom side of the Find and Replace box, you will see matching all the cells formatted and on the left bottom side, you will notice the count. To select all the cells, press CTRL + A and then click on the Close button, and then all the cell color gets changed.
In case when returning cells are required in a given column or range only, you can do this by choosing the range and then clicking on the Find All option.
How Excel Count Colored Cells using Filter and SUBTOTAL
To count colored cells in a sheet, you can follow the steps given below:
Using the SUBTOTAL function to count colored cells once filtering is applied.
Let’s understand this method in detail:
In the given dataset, you can see two background colors, green and orange.
Put the formula in any cell below the dataset.
Formula =SUBTOTAL(102, E1:E20)
- Choose the headers.
- Click on the Data and choose Sort & Filter option. From this menu, choose Filter and you will see the filter is applied to all the headers.
- Now, click on a filter drop-down.
- Open the “Filter by Color” option and choose the color. The given dataset has two colors that’s why the filter shows two colors when filtering these cells.
- Once the filters are applied, the SUBTOTAL function changes and returns the number of cells that appear after filtering.
How Excel Count Colored Cells using VBA Code
Do you remember the user-defined function we mentioned earlier in this post?
VBA is considered one of the safest methods when you need to execute Excel-related tasks. For beginners, applying VBA coding is not an easy thing because VBA uses advanced features of Excel.
Let’s get to know how you can apply VBA coding to count colored cells in Excel:
- First, press ALT + F11 from the keyboard or else you can open the Developer menu. From this menu, click on the “Visual Basic” option, it will open the Visual Basic Editor.
- Now, from the menu bar, choose Insert and then click on the Module option in the code window.
Below you can see the code. Just copy and paste it into the code window.
Function Count_Colored_Cells(ColorCells As Range, DataRange As Range)
Dim Data_Range As Range
Dim Cell_Color As Long
Cell_Color = ColorCells.Interior.ColorIndex
For Each Data_Range In DataRange
If Data_Range.Interior.ColorIndex = Cell_Color Then
Count_Colored_Cells = Count_Colored_Cells + 1
End If
Next Data_Range
End Function
Copy
Note that you don’t need a Sub Procedure to run VBA, this is a User-Defined Function that’s why to click on the don’t click the Run option once the code is done.
Now, get back to the dataset and define cells with colors. Later on, put the following formula in cell F5.
=Count_Colored_Cells(E5, $B$5:$B$16)
In this formula the arguments are used as:
Count_Colored_Cells: It is the User-Defined function created in the VBA code to count colored cells.
E5: Blue color – defined cell
$B5:$B$16: It is the range of the dataset used with the colored cells.
Press Enter key.
Now, you will get the count of color-defined cells, for instance, the dataset has 4 Blue cells, that’s why Blue color-defined cell E5 gives the count 4. Simply, drag the cell by Fill Handle to get all the counts of colored cells in the sheet.
As you can see, the Orange colored cells are 5, the user-defined Count_Colored_Cells function gives us the count 5.
