Count Colored Cells in Excel

There is no built-in function to count colored cells in Excel, but below mentioned are three different methods to do this task.

1. Count colored cells by using the Auto Filter option
2. Count colored cells by using the VBA code
3. Count colored cells by using the FIND method

Now, let us discuss each of them in detail –

For this example, let us look at the below data.

As we can see, each city is marked with different colors. So, we need to count the number of cities based on cell color.

As we can see, all the colors in the data. Now, we must choose the color that we want to filter.

Similarly, now we must choose other colors to get the count of the same.

So, blue-colored cells count to five now.

VBA's street smart techniques help us reduce time consumption at our workplace for some complicated issues.

We can reduce time, but we can also create our functions to fit our needs. For example, we can create a function to count cells based on color in one such function. Below is the VBA code to create a function to count cells based on color.

Code:

Function Color_Cell_Count(ColorCell As Range, DataRange As Range)

Dim Data_Range As Range
Dim Cell_Color As Long

Cell_Color = ColorCell.Interior.ColorIndex

For Each Data_Range In DataRange

If Data_Range.Interior.ColorIndex = Cell_Color Then
Color_Cell_Count = Color_Cell_Count + 1
End If

Next Data_Range

End Function

Then, copy and paste the above code to your module.

This code is not a SUB Procedure to run. Rather, it is a “User Defined Function” (UDF).

The first line of the code “Color_Cell_Count” is the function name. Now, we must create three cells and color them as below.

Now, we must open the function “Color_Cell_Count” in the G2 cell.

Even though we do not see the syntax of this function, the first argument is what color we need to count, so we must select cell F2.

The second argument is to select the range of cells as D2:D19.

Now, close the bracket and press the "Enter” key. As a result, it will provide the count of cells with the selected cell color.

Like this, with the help of UDF in VBA, we can count cells based on cell color.

We can also count cells based on the FIND method as well.

• Step 1: First, we must select the range of cells where we need to count cells.

• Step 2: Now, we need to press Ctrl + F to open the FIND dialog box.

• Step 3: Now, click on “Options>>.”

• Step 4: Consequently, it will expand the "Find” dialog box. Now, we must click on the “Format” option.

• Step 5: Now, it will open up the “Find Format” dialog box. We need to click on the “Choose Format From Cell” option.

• Step 6: Now, move the mouse pointer to see the pointer to select the format cell in excel that we are looking to count.

• Step 7: We will select the cell formatted as the desired cell count. We have chosen the F2 cell as the desired cell format, and now we can see the preview.

• Step 8: Now, click on the "Find All" option to get the count of the selected cell format count of cells.

So, a total of 6 cells were found with selected formatting colors.

• The provided VBA code is not a Subprocedure in VBA; it is a UDF.
• The SUBTOTAL contains many formulas used to get the result only for visible cells when the filter is applied.
• We do not have any built-in function in Excel to count cells based on the color of the cell.