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