Author: Oscar Cronquist Article last updated on December 21, 2017

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you tweak the conditional formatting formula to count conditionally formatted cells.

Recommended article:

Working with Conditional Formatting formulas

In this post I am going to try to explain formula basics in conditional formatting. It is really good if […]

The user defined function in cell D2 counts cells with the same cell color as cell C2. Conditional formatting was applied to cell range A2:A20, formula:
=A2

There are 10 red cells in cell range A2:A20 and the user defined function returns 10 i cell D2. This udf can only compare and count cell colors and conditional formatting formulas.

### VBA code

```Function CountCFCells(rng As Range, C As Range)
Dim i As Single, j As Long, k As Long
Dim chk As Boolean, Str1 As String, CFCELL As Range
chk = False
For i = 1 To rng.FormatConditions.Count
If rng.FormatConditions(i).Interior.ColorIndex = C.Interior.ColorIndex Then
chk = True
Exit For
End If
Next i
j = 0
k = 0
If chk = True Then
For Each CFCELL In rng
Str1 = CFCELL.FormatConditions(i).Formula1
Str1 = Application.ConvertFormula(Str1, xlA1, xlR1C1)
Str1 = Application.ConvertFormula(Str1, xlR1C1, xlA1, , ActiveCell.Resize(rng.Rows.Count, rng.Columns.Count).Cells(k + 1))
If Evaluate(Str1) = True Then j = j + 1
k = k + 1
Next CFCELL
Else
Exit Function
End If
CountCFCells = j
End Function
```

The code is tested in excel version 2010.

### Where do I put the code?

1. Open the VB Editor or press short cut key Alt+F11.
2. Click "Insert" on the menu
3. Click "Module"
4. Paste code to module
5. Exit VB Editor
6. Save the workbook as an *.xlsm file