Excel VBA用户定义的函数，该函数可计数有条件格式的单元格

Question

我正在尝试编写一个UDF，以计算具有条件格式的单元格数。我写了以下词，就像魅力一样：

Sub SumCountByConditionalFormat()
Dim cellrngi As Range
Dim cntresi As Long
cntresi = 0
Set cellrngi = Sheets("Sheet3").Range("I2:I81")
For Each i In cellrngi
    If i.DisplayFormat.Interior.Color <> 16777215 Then
    cntresi = cntresi + 1
    End If
Next i
end sub

我尝试将其转换为带有以下代码的UDF：

Function CountCellsByColor1(rData As Range) As Long
Dim cntRes As Long
Application.Volatile
cntRes = 0
For Each cell In rData
    If cell.DisplayFormat.Interior.Color <> 16777215 Then
        cntRes = cntRes + 1
    End If
Next cell
CountCellsByColor1 = cntRes
End Function     

但是，当我尝试UDF时，我会得到#Value！回。我真的不确定为什么和任何帮助将不胜感激。

Answer 1

您可以使用Evaluate

在UDF中访问DisplayFormat

Function DFColor(c As Range)
    DFColor = c.DisplayFormat.Interior.Color
End Function

Function CountCellsByColor1(rData As Range) As Long
    Dim cntRes As Long, clr As Long, cell As Range
    cntRes = 0
    For Each cell In rData.Cells
        'Evaluate the formula string in the context of the
        '  worksheet hosting rData
        clr = rData.Parent.Evaluate("DFColor(" & cell.Address() & ")")
        If clr <> 16777215 Then
            cntRes = cntRes + 1
        End If
    Next cell
    CountCellsByColor1 = cntRes
End Function