Search
• Dp

# How to count colored cells in MS Excel

In this tutorial, let's learn How to count colored cells in MS Excel.

Here, we would be using the VBA method and this is the most easiest method to count colored cells in MS excel.

This is the example that I would like to show you now and let us count the number of red and orange colored cells.

First, go to VBA window by pressing Alt+F11 shortcut.

1. You will land into this VBA window now.

2. Click on Insert>> Module to insert a new module.

3. Copy the below code and paste it here in the module.

```Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorValue Then
TotalCount = TotalCount + 1
End If
Next rCell
GetColorCount = TotalCount
End Function```

4. Save your excel and close this VBA window and go back to the Excel sheet.

Using this VBA code, we have created a custom function called "Getcolorcount"

And this function can be used like any other function now.

Here, we have two colors red and orange in the sheet.

In J4 Cell, type the formula as =getcolorcount(F5:F38,I4)

here, F4:F38 represents the data which to be counted and

I4 is the background color, what to be counted.

Press enter and you will get the result as 9. You can cross verify the figure whether it is right or not.

Similarly, let us count the red colored cells by typing formula in cell J5 as =getcolorcount(F5:F38,I5) and press enter to see the result as 8.

So friends, this is all about the How to count colored cells in MS Excel.

Watch this video tutorial for better understanding:  