How to make GetColorCount (custom function) work with Conditional Formatting?

[slam]

Many years ago, I found this incredibly helpful piece of code that I have used hundreds of times since:

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

I use it with a formula like this:

=@GetColorCount($AF2:$AG2,$AH$1)

Where AH1 is a fill color I've defined, then it looks in AF2:AG2 for matching fill colors, and gives me a count of the matches.

However, It seems it does not work with conditional formatting. Is there a solution to this?

Thanks

 

[Fluff]

Is AH1 manually filled, or is that also from CF?

 

[slam]

Is AH1 manually filled, or is that also from CF?

AH1 is a manual Fill Color. I thought I might have seen where you were going with this, so I tried to make AH1 colored via conditional formatting too. However, then it doesn't seem to recognize that the AH1 is filled at all, so it just thinks AH1 has no fill, and counts all the cells in the range (which, incidentally, I changed to $D2:$AG2, so the result is 30).

I also verified with the hex code that I'm using the exact same color in conditional formatting as in AH1, which is FF0000.

 

[Fluff]

Ok, how about

Function GetColorCount(CountRange As Range, CountColor As Range)
   Dim CountColorValue As Long
   Dim TotalCount As Long
   Dim rcell As Range
   
   CountColorValue = CountColor.Interior.ColorIndex
   For Each rcell In CountRange
     If Evaluate("getcfcolour(" & rcell.Address(, , , True) & ")") = CountColorValue Then
       TotalCount = TotalCount + 1
     End If
   Next rcell
   GetColorCount = TotalCount
End Function
Private Function GetCFColour(Cl As Range) As Long
   GetCFColour = Cl.DisplayFormat.Interior.ColorIndex
End Function

 

[slam]

That worked, thank you! And it looks like you made it work with fill or conditional formatting, so my other instances of using it with fill in the same workbook are not impacted. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.