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.

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:

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.

Count cells by conditional formatting color

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
CountCFCells = "Color not found"
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

vba editor paste code here

Download *.xlsm file

Count cells by conditional formatting color (vba).xlsm