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

**Table of Contents**hide

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

**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**–

`=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.

**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**–

`=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.

**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**–

`=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.

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

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