Hi,
In the attached file there is a VBA code that gives me a function that counts by color.
The problem is that it doesn't work with conditional formatting
In the table the yellow cells were created from conditional design
And in the sample cell on this side is normal coloring
what can we do??
How do you count the yellow cells in data based on conditional formatting?
Thank you!
Leah
You can't count conditionally formatted colors. In stead, you should use COUNTIF and use the condition for setting the format as the criteria. In your example file you could use:
=COUNTIF(INDEX(A2:H22,,MATCH(J4,A1:H1,0)),">300")
Hi Riny, I disagree, it is possible
Function countRowsWithConditionalColor(data_range As Range, cell_color As Range) As Long
Dim cntRes As Long, cel As Range
For Each cel In data_range
On Error Resume Next
If cel.DisplayFormat.Interior.Color = cell_color.Interior.Color Then
cntRes = cntRes + 1
End If
Next cel
Err.Clear
On Error GoTo 0
countRowsWithConditionalColor = cntRes
End Function
Attached a copy of the (working) file
Hi Hans,
It doesn't work properly though. DisplayFormat cannot be reliably used in a UDF - try updating some of the values so they go yellow and your function won't update.
You can work around it using Evaluate but it's not a good idea in my opinion.
It's as reliable as the color you choose/pick. That's where the problem lies
I disagree. It will not work properly in a UDF - Microsoft even states that in the documentation.
