How to Count Colored Cells in Excel with VBA (4 Easy Methods)

We often use fill color, font color, or conditional formatting color in our worksheets for many reasons. Along with that sometimes we need to count that colored cells. We can use function or command for that but using VBA is a quite smart and fast way. So today in this article we’ll demonstrate to you 4 useful macros to count colored cells in Excel with VBA.

How to Count Colored Cells with Excel VBA: 4 Suitable Ways

First of all, get introduced to our dataset that represents some students’ obtained marks in two subjects. We used fill colors green and yellow to highlight marks greater than 80 and less than 50 consecutively.

1. Apply VBA Code to Count Cells by Fill Color

First, we’ll learn how to count cells by fill colors with VBA macro. We’ll make a User Defined Function (UDF) first and then will apply it as a worksheet function. Our function name will be CountCellBy_FillColor. It will have two arguments- range and cell color.

Steps:

• Press Alt + F11 to open the VBA window.
• Then click as follows to insert a module: Insert > Module.

• Next, type the following codes in the module-

Function CountCellBy_FillColor(CellRange As Range, CellColor As Range)
Dim FillColor As Integer
Dim FillTotal As Integer
FillColor = CellColor.Interior.ColorIndex
Set rCell = CellRange
For Each rCell In CellRange
If rCell.Interior.ColorIndex = FillColor Then
FillTotal = FillTotal + 1
End If
Next rCell
CountCellBy_FillColor = FillTotal
End Function

• No need to run the codes, just go back to your worksheet and you will get the function available there.

• We’ll count for the green color so we placed the sample color in Cell F5.
• Insert the following formula in Cell G5–

• Finally, just hit the Enter button and you will get the count for the green color.

2. Run a VBA Code to Count Cells by Font Color in Excel

Like the previous method, now we’ll create user defined function- CountCellsBy_FontColor that will count cells by font color. The codes are pretty much the same.

So we modified the dataset and applied font color instead of the fill color.

Steps:

• Firstly, follow the first two steps from the first method to open the VBA window and insert a module.
• Later, write the following codes in the module-

Function CountCellsBy_FontColor(cell_range As Range, CellFont_color As Range) As Long
Dim FontColor As Long
Dim CurrentRange As Range
Dim FontRes As Long
Application.Volatile
FontRes = 0
FontColor = CellFont_color.Cells(1, 1).Font.Color
For Each CurrentRange In cell_range
If FontColor = CurrentRange.Font.Color Then
FontRes = FontRes + 1
End If
Next CurrentRange
CountCellsBy_FontColor = FontRes
End Function

• After that turn back to your sheet.

• We’ll count for red font color, so insert the following formula in cell G5–

• Then just hit the Enter button to finish.

Look, there are 4 cells with red font color and our function is returning the same result.\

3. Use VBA to Count Colored Cells by Row in Excel

Imagine a case, there are filled colors in three columns and we want the fourth column to return the output 1 if at least two cells remain green in the row and if not then it will return 0. To do this, we’ll create another function named Colorby_Row. We used RGB (0,200,0) for the green color. You can change it in the codes.

Here’s our modified dataset for the method, we added another column.

Steps:

• Firstly, follow the first two steps from the first method to open the VBA window and insert a module.
• Next, insert the following codes in the module-

Function Colorby_Row(rowColor1 As Range, rowColor2 As Range, rowColor3 As Range) As String
Dim rowResult As String
Dim rowCounter As Integer
mColor1 = rowColor1.Interior.Color
mColor2 = rowColor2.Interior.Color
mColor3 = rowColor3.Interior.Color
green = RGB(0, 200, 0)
rowCounter = 0
If mColor1 = green Then
rowCounter = rowCounter + 1
End If
If mColor2 = green Then
rowCounter = rowCounter + 1
End If
If mColor3 = green Then
rowCounter = rowCounter + 1
End If
If rowCounter >= 2 Then
rowResult = 1
Else
rowResult = 0
End If
Colorby_Row = rowResult
End Function

• Then go back to your sheet.

• Insert the following fiormula in Cell F5 and press the Enter button–

• Lastly, use the Fill Handle tool to copy the formula for the other rows.

Here’s the output. You can use this formula for the cells with conditional formatting too.

4. Embed VBA to Count and Sum Cells by Conditional Formatting Color

The first two methods could only count and sum cells with fill or font color. Now we’ll create a macro that will be able to count and sum cells by conditional formatting color.

So we modified the dataset again by applying conditional formatting.

Steps:

• First, select the data range.

• Then follow the first two steps from the first method to open the VBA window and insert a module.

• After that, type the following codes in the module-

Sub SumNCountByConditionalFormattingColor()
Dim mitRefColor As Long
Dim SampleColor As Range
Dim mitRes As Long
Dim mitSum
Dim CountCells As Long
Dim mitCurrCell As Long
On Error Resume Next
mitRes = 0
mitSum = 0
CountCells = Selection.CountLarge
Set SampleColor = Application.InputBox( _
"Choose sample color:", "Select a cell which has sample color", _
Application.Selection.Address, Type:=8)
If Not (SampleColor Is Nothing) Then
mitRefColor = SampleColor.Cells(1, 1).DisplayFormat.Interior.Color
For mitCurrCell = 1 To (CountCells)
If mitRefColor = Selection(mitCurrCell).DisplayFormat.Interior.Color Then
mitRes = mitRes + 1
mitSum = WorksheetFunction.Sum(Selection(mitCurrCell), mitSum)
End If
Next
MsgBox "Cell Count= " & mitRes & vbCrLf & "Sum of Colored Cells= " & mitSum & vbCrLf & vbCrLf & _
"Color Code= " & Left("000000", 6 - Len(Hex(mitRefColor))) & _
Hex(mitRefColor) & vbCrLf, , "Count and Sum Cells by Conditional Formatting Color"
End If
End Sub

• Later, press the Run icon to run the codes and it will open an input box to select the sample color from the selected range.

• Then select a cell with the preferred color. We’ll count and sum for the green color, so picked Cell C6.
• Finally, just hit the Enter button.

Now see a notification box showing the cells count and sum along with the color code.

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to count colored cells in Excel with VBA. Feel free to ask any question in the comment section and please give me feedback.

<< Go Back to Colored Cells | Count Cells | Formula List | Learn Excel