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 formulas in conditional formatting

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

Comments(3) Filed in category: Conditional formatting, Excel

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