这个答案与@RicardinhoL的答案相同，但有三个小区别：1）使用一个可选的颜色参数，默认值，你在问题2）检查范围是否不是其他错误发生3）使用函数的名称作为计数器，因为它的工作原理就像一个变量本身。
必须返回Long，因为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

我认为这是计算特定列的单元格或范围所需的。

Function SumCellsByColor(colorRange As Range, targetColor As Long) As Long
    Dim cell As Range
    Dim count As Long
    
    count = 0
    
    For Each cell In colorRange
        If cell.Interior.color = targetColor Then
            count = count + 1
        End If
    Next cell
    
    SumCellsByColor = count
End Function

Sub ExampleUsage()
    Dim rng As Range
    Dim count As Long
    
    ' Define the range of cells in which you want to count
    Set rng = Range("A18:C22")
    
    ' Call the SumCellsByColor function and pass the range and target color.
    count = SumCellsByColor(rng, RGB(255, 0, 0)) ' For example, the color red
    
    ' Print the result
    MsgBox "Number of cells with red color: " & count
End Sub

要让UDF返回一个数组，您只需将数组设置为等于返回行中的函数。
例如，假设您显然希望返回一个布尔数组，该数组对应于区域中的单元格是否着色：

Option Explicit

Function FillIndicator(color As Range)
    Dim v  'variable to loop through array
    Dim al As Object 'collect the test results
    
Set al = CreateObject("System.Collections.ArrayList")

'run the test on each cell
For Each v In color
    al.Add v.Interior.ColorIndex <> -4142
Next v

'Set the Function equal to an array
FillIndicator = al.toarray

End Function

注意倒数第二行，其中FillIndicator被设置为等于数组。
公式将返回一个TRUE/FALSE数组，具体取决于Interior.ColorIndex属性。
然后，您可以将其视为任何其他数组函数。