CODEWITHSUNDEEP
excelvba
DBA108642
DBA108642

Reputation: 2112

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.

Upvotes: 2

Views: 2066

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

You can work around the inability to access DisplayFormat in a UDF using 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

Upvotes: 11

Related Questions