Excel - counting cells with a specific colour (including conditional formatting)

[MZ1]

Hi Everyone,
this is my first post on this forum. I'm writing cause I can't find a satisfying solution to my problem.
I need to count the number of cells with specific Interior Colour. For now, to do so I was using Visual Basic function as below:

Function CountColor(CellsRange As Range, ReferenceCell As Range)
 Dim indRefColor As Long
 Dim Format As Range

Application.Volatile
CountColor= 0
indRefColor = ReferenceCell.Interior.Color
For Each cell In CellsRange

    If indRefColor = cell.Interior.Color Then

     CountColor= CountColor+ 1

    End If

Next
End Function

To sum up:
I want to modify the above function so that it counts for cells with the same interior colour as the selected one (and includes cells with conditional formatting).

Thank you for your time and help.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Function CountColor(CellsRange As Range, ReferenceCell As Range)
   Dim indRefColor As Long
   Dim Format As Range
  
   Application.Volatile
   CountColor = 0
   indRefColor = ReferenceCell.Interior.Color
   For Each cell In CellsRange
      If Evaluate("CFColour(" & cell.Address & ")") = indRefColor Then
         CountColor = CountColor + 1
      End If
   Next
End Function
Function CFColour(Cl As Range) As Double
   CFColour = Cl.DisplayFormat.Interior.Color
End Function

 

[Peter_SSs]

If Evaluate("CFColour(" & Cl.Address & ")") = indRefColor Then

cell.Address?

I was also wondering if the ReferenceCell might also be coloured by Conditional Formatting?

 

[Fluff]

cell.Address?

Oops, well spotted, I've changed code in post#2

I was also wondering if the ReferenceCell might also be coloured by Conditional Formatting?

Good point, this will handle that situation.

Function CountColor(CellsRange As Range, ReferenceCell As Range)
   Dim indRefColor As Long
   Dim Format As Range
   
   Application.Volatile
   CountColor = 0
   indRefColor = Evaluate("cfcolour(" & ReferenceCell.Address & ")")
   For Each cell In CellsRange
      If Evaluate("CFColour(" & cell.Address & ")") = indRefColor Then
         CountColor = CountColor + 1
      End If
   Next
End Function
Function CFColour(Cl As Range) As Double
   CFColour = Cl.DisplayFormat.Interior.Color
End Function

 

[MZ1]

Thank you very much. It works perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.