Apply an Excel array formula with a custom VBA function

Question

I'm trying to use Excel array formulae for summing up the number of cells in a columnar range (an array if you will), which have a background color.

This working function checks whether a cell has a background color:

Function FillIndicator(color As Range) As Integer
If color.Interior.ColorIndex = -4142 Then
    FillIndicator = 0
Else
    FillIndicator = 1
End If
End Function

If I were to eschew array formulae, and my range were, say, C5:C7, I would write something like:

=FillIndicator(C5) + FillIndicator(C6) + FillIndicator(C7)

My attempt at using an array formula:

=SUM(FillIndicator(C5:C7))

Pressing Ctrl+Shift+Enter gets me a #VALUE error.

Note: I think I'm using Excel 365 version 2208.

Answer 1

This answer is the same as @RicardinhoL answer with three small differences: 1) use an Optional color parameter with default value that you use in question 2) check if range is nothing else an error occurs 3) use as counter the name of the function, since it works like a variable itself.

Must to return Long because there are many-many cells in excel!

Public Function CountIfColor(rngClr As Range, Optional thisColor As Long = -4142) As Long
   Dim r As Range
   If Not rngClr Is Nothing Then
      For Each r In rngClr
         If r.Interior.ColorIndex = thisColor Then CountIfColor = CountIfColor + 1
      Next
   End If
End Function