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.
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?
- Open the VB Editor or press short cut key Alt+F11.
- Click "Insert" on the menu
- Click "Module"
- Paste code to module
- Exit VB Editor
- Save the workbook as an *.xlsm file