Excel VBA用户定义函数,可使用条件格式对单元格进行计数 [英] Excel VBA User Defined Function that counts cells with conditional formatting
本文介绍了Excel VBA用户定义函数,可使用条件格式对单元格进行计数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试编写一个UDF,该UDF对具有条件格式的单元格的数量进行计数.我写了下面的子程序,看起来很像魅力:
I am trying to write a UDF that counts the number of cells that have conditional formatting. I wrote the following sub that works like a charm:
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:
and I tried to convert it to a UDF with the following code:
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!回来.我真的不确定为什么,任何帮助将不胜感激.
However when I try the UDF i get a #VALUE! returned. I'm really not sure why and any help would be much appreciated.
推荐答案
您可以解决无法使用Evaluate
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
这篇关于Excel VBA用户定义函数,可使用条件格式对单元格进行计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文