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

 

[Joe4]

Welcome to the Board!

That code will only count cells that are colored manually. It will not count cells colored by Conditional Formatting.
In order to do that, you will need to use code like shown here: ExcelSirJi | VBA Code To Count Color Cells With Conditional Formatting

 

[mikerickson]

You missed a property in the test

If C.DisplayFormat.Interior.Color = TargetColor Then Count = Count + 1

Although, I am doubtful that this UDF could be used in a worksheet formula. Refering to the .DisplayFormat in a UDF will return the correct value when called from VBA, but gives a #VALUE error when called by a worksheet formula.
It might be best to test for the condition that causes the CF color rather than for the color itself.

 

[tbilderbac]

I am trying to count the RED cells and as you can see I have changed the Set rng = Sheet1.Range ("G18:X18} and the color to the RED (255,0,0) and changed the display to AD18? it is not showing? What am I doing?

 

[Fluff]

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

 

[tbilderbac]

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

So I am unclear with this? If I put this in a module and then run it it should pull up the number of reds in a row? Where do I put in the color and the range of cells? Sorry not real versed in coding?

 

[Fluff]

You use it exactly the same as the code you initially posted.

 

[tbilderbac]

You use it exactly the same as the code you initially posted.

So am I replacing the "cfcolour(" & ")" with something or just put this code in and go with it?

 

[Fluff]

Just replace the code you posted, with the code I posted.

 

[tbilderbac]

So am I replacing the "cfcolour(" & ")" with something or just put this code in and go with it?

OMG It works!! that is great thanks