Counting cells by conditional formatting color

[tbilderbac]

I have a spreadsheet that I am inputting golf scores in and for certain colors I am wanting to auto count that color. I got some code that should count the number of red cells [Conditional formatting] in the row. The formula is =CountByColor(G18:X18,E2) but it is not counting up the red cells? I have the target cell conditionally formatted to the color red so it should be working?

Public Function CountByColor(CellRange As Range, TargetCell As Range)

Dim TargetColor As Long, Count As Long, C As Range

TargetColor = TargetCell.DisplayFormat.Interior.Color
For Each C In CellRange
If C.DisplayFormat.Color = TargetColor Then Count = Count + 1

Next C
CountByColor = Count

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[akosikikit]

Hi,

I tried using this same code but it does not appear to work for me. Apologies as I am new to coding. I have the same set up as tbilderbac where on one worksheet I have users enter values, and there is a conditional formatting set that changes the cell color whenever a criteria is met. I am now trying to consolidate the results on a separate worksheet by counting the number of colored cells.

Hope you can help me on this one.

Here's how I tried to run the said function:

Do I need to save the Excel file as .xlsm first for it to run? PLease advise.

 

[Fluff]

Do I need to save the Excel file as .xlsm first for it to run?

Yes you do.

 

[katinator91]

it worked then stopped working now I am getting the #name? error?

 

[rjheibel]

You could do it like

Public Function CountByColor(CellRange As Range, TargetCell As Range)

Dim TargetColor As Long, Count As Long, C As Range

TargetColor = Evaluate("cfcolour(" & TargetCell.Address & ")")
For Each C In CellRange
If Evaluate("Cfcolour(" & C.Address & ")") = TargetColor Then Count = Count + 1

Next C
CountByColor = Count
End Function
Function CFColour(Cl As Range) As Double
   CFColour = Cl.DisplayFormat.Interior.Color
End Function

Hey, I was excited to find this code but it does not seem to count the cells correctly for me. When I try to find the number of gray cells in January, it results in 12 instead of 9 as seen in the screenshot. If not for the first condition marked as "stop if True", the other Sunday and Saturday blocks would be highlighted gray, so I assume this code somehow is checking if a conditional format exist that to make the cell a certain color??? Is there a way of only looking at the end result of the color based on all conditions?

 

[Fluff]

Is there a way of only looking at the end result of the color based on all conditions?

That's exactly what the code does.

 

[rjheibel]

That's exactly what the code does.

hmmm... ya for some reason its three of the white cells as gray, I even verified by only selecting those individual cells.