COUNTIF function but with conditional formatted cells

Occasional Contributor



I have a spreadsheet that uses conditional formatting via a drop down list that the cell changes colour depending on result. I want to be able to count the number of cells that are certain colours. I have watch tutorials for Get.cell but that can't work with condition formatting. I've also looked at setting up VBA but can't seem to get that to work either wasn't sure if this is because of the formatting.

Please help is there a solution to this?


1 Reply


Below is VBA to count conditionally formatted colors by leveraging AutoFilter to select by color and DisplayFormat.Interior.Color to get a cell's displayed color created by any means including conditional formats


Function CountColour(ByRef oRange As Range, ByRef oSample As Range) As Long

' This function leverages AutoFilter's ability to select cells by color

' oRange is the cells (arranged in a column) to search
' oSample is a cell that is displaying the color to search for
' Alternatively, we can use lColor as a parameter instead of oSample for those
' situations when we do not have a sample to work with. See below on how to set lColor

Dim lColor As Long

' Displayed color whether from conditional formatting or not
lColor = oSample.DisplayFormat.Interior.Color

' Excel applies AutoFilter to the first non-blank cell; thus, if cells above are
' blank, they will remain visible. We don't want that. Since we are counting conditionally
' formatted cells, we should remove non-conditionally formatted cells from oRange
Set oRange = oRange.SpecialCells(xlCellTypeAllFormatConditions)

' Apply AutoFilter
oRange.AutoFilter Field:=1, Criteria1:=lColor, Operator:=xlFilterCellColor

' Count visible cells
CountColour = oRange.SpecialCells(xlCellTypeVisible).Cells.Count

' The AutoFilter cells is always visible but may not be the color we want
If oRange.Cells(1).DisplayFormat.Interior.Color <> lColor Then CountColour = CountColour - 1

' Return display to normal

End Function