'Excel VBA User Defined Function that counts cells with conditional formatting
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
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
However when I try the UDF i get a #VALUE! returned. I'm really not sure why and any help would be much appreciated.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
