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.

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
    Next CFCELL
Else
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