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

The sample dataset contains students’ marks in two subjects. We will fill with green or yellow to highlight marks greater than 80 or less than 50 respectively.

Method 1 – Apply VBA Code to Count Cells by Fill Color

Steps:

• Press Alt + F11 to open the VBA window.
• Select Insert then Module.

• Enter 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

• To count the green cells, place the cursor beside Cell F5.
• Insert the following formula in Cell G5.

• Hit the Enter button and you will get the green cells count.

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

Steps:

• Press Alt + F11 to open the VBA window.
• Select Insert then Module.
• Enter 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

• Return to the worksheet.

• To count the red cells, insert the following formula in cell G5.

• Hit Enter.

Method 3 – Use VBA to Count Colored Cells by Row in Excel

In this dataset we have added another column and we need to count the number of cells of the same color by row.

Steps:

• Press Alt + F11 to open the VBA window.
• Select Insert then Module.
• Paste the following code 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

• Go back to the worksheet.

• Insert the following formula in Cell F5 and press the Enter button.

• Use the Fill Handle tool to copy the formula for the other rows.

Here is the result.

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

Conditional formatting has been applied to the dataset.

Steps:

• Select the data range.

• Press Alt + F11 to open the VBA window.
• Select Insert then Module.
• Enter the following code 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

• Select the Run icon to run the code. It will open an input box to select the sample color from the selected range.

• Select a cell with the preferred color, in this example Cell C6 has been selected.
• Hit the Enter button.

A notification box showing the cells count and sum along with the color code is returned.

Download Practice Workbook

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