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.


Download Practice Workbook

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


4 Suitable Ways to Count Colored Cells with Excel VBA

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.

How to Count Colored Cells in Excel with VBA


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.

Apply VBA to Count Cells by Fill Color In Excel

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

Apply VBA to Count Cells by Fill Color In Excel

  • We’ll count for the green color so we placed the sample color in Cell F5.
  • Insert the following formula in Cell G5
=CountCellBy_FillColor($C$5:$D$11,F5)
  • Finally, just hit the Enter button and you will get the count for the green color.

Read More: How to Count Colored Cells In Excel Without VBA (3 Methods)


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.

Use Excel VBA to Count Cells by Font Color

Steps:

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.

Use Excel VBA to Count Cells by Font Color

  • We’ll count for red font color, so insert the following formula in cell G5
=CountCellsBy_FontColor($C$5:$D$11,F5)
  • 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.\

Read More: How to Count Cells with Specific Text and Fill/Font Color in Excel (4 Ways)


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.

Use VBA to Count Colored Cells by Row in Excel

Steps:

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.

Use VBA to Count Colored Cells by Row in Excel

  • Insert the following fiormula in Cell F5 and press the Enter button
=Colorby_Row(C5,D5,E5)

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

Read More: Excel Formula to Count Colored Cells in a Row (2 Effective Methods)


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.

Embed VBA to Count and Sum Cells by Conditional Formatting Color

Steps:

  • First, select the data range.

Embed VBA to Count and Sum Cells by Conditional Formatting Color

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

Read More: Count Cells by Color with Conditional Formatting in Excel (3 Methods)


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.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo