counting by conditional formatting color

[tbilderbac]

I got this code:

-----------
Public Function CountByColor2(CellRange As Range, TargetCell As Range)

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

TargetColor = TargetCell.Interior.ColorIndex
For Each C In CellRange
If C.Interior.ColorIndex = TargetColor Then Count = Count + 1
Next C
CountByColor2 = Count


End Function
------------

I created the formula:
=CountByColor2(RC[-23]:RC[-6],R[-16]C[-25])

and wanting it to count up the RED blocks: [should be 5]

 

[Craggs82]

You'll need to add "DisplayFormat" to account for the fact that the formatting is surface level through conditional formatting. soooo...

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

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

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

End Function

 

[Fluff]

Is the target cell manually coloured or is that also done with conditional formatting?

@Craggs82 you cannot use DisplayFormat in a UDF.

 

[Craggs82]

every day is an annoying Microsoft school day

best option would be re-write the formatting rules in the udf? oof.

edit. typo.

 

[tbilderbac]

Thank you, the TargetCell is done with Conditional formatting, thought I had to do that to make the code to work? So I just did a quick rule to get the same color for the TargetCell as the row cells. I will add in the DisplayFormat to the string. and try

 

[Fluff]

The DisplayFormat will not work, as you cannot use it when calling the function from the sheet.

 

[tbilderbac]

So I copied the code:

------
Option Explicit

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

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

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


End Function
------
and still not working?

=CountByColor(RC[-23]:RC[-6],R2C5)

 

[Fluff]

You can use this workround, although you would probably be better of with a normal formula that uses the same rules as your CF

Public Function CountByColor2(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
   CountByColor2 = Count


End Function
Function CFColour(Cl As Range) As Double
   CFColour = Cl.DisplayFormat.Interior.Color
End Function

 

[tbilderbac]

Not working, could it be it is not working because I have excel 365?

 

[Fluff]

In what way isn't it working?