Conditional formatting UDF value error

[YasserKhalil]

Hello everyone
I have UDF function that enables me to count colored cells (conditional formatting cells) and sum the values of colored cells

Function CountCFCells(Rng As Range, C As Range, bCount As Boolean)
    Dim I As Single, J As Long
    Dim Chk As Boolean, Str1 As String, CFCELL As Range
    Application.Volatile
    Chk = False
    
    For I = 1 To Rng.FormatConditions.Count
        If Rng.FormatConditions(I).Interior.ColorIndex = C.Interior.ColorIndex Then
            Chk = True
            Exit For
        End If
    Next I
    
    J = 0
    If Chk = True Then
        For Each CFCELL In Rng
            Str1 = CFCELL.FormatConditions(I).Formula1
            Str1 = Application.ConvertFormula(Str1, xlA1, xlR1C1)
            Str1 = "=" & CFCELL.Address & Split(Str1, "]")(UBound(Split(Str1, "]")))
            If bCount = False Then
                If Evaluate(Str1) = True Then J = J + 1
            Else
                If Evaluate(Str1) = True Then J = J + CFCELL
            End If
        Next CFCELL
    Else
        CountCFCells = "Color Not Found"
        Exit Function
    End If
    CountCFCells = J
End Function

I need to update results of the UDF .. as I noticed the following ..if I select any cell withing A1:B15 which has conditional formatting rules, the results give a value error ..but when selecting any other cell , everything goes right..
Can I fix this debug?

[skywriter]

Maybe this.
Here's the formula I used in cell G3 of your sample sheet.

=CountIF(B1:B15,"<=20")+CountIF(A1:A15,">=20")

[TMS]

Actually, the problem occurs if you select a cell anywhere in column A or column B.

A workaround is to add this near the start of your code:

If ActiveCell.Column <= 2 Then
    CountCFCells = "error"
    Exit Function
End If

It doesn't make the problem go away ... but you could change the error message to say "don't select this cell!"

Regards, TMS

[YasserKhalil]

Mr. PCI
That's awesome .. really fascinating
Exactly as needed
Thank you very much for great help

[PCI]

See next code

Option Explicit

Function CountCFCells(Rng As Range, C As Range, bCount As Boolean)
    Dim I As Single, J As Long
    Dim Chk As Boolean, Str1 As String, CFCELL As Range
    Application.Volatile
    Chk = False
    
    For I = 1 To Rng.FormatConditions.Count
        If Rng.FormatConditions(I).Interior.ColorIndex = C.Interior.ColorIndex Then
            Chk = True
            Exit For
        End If
    Next I
    
    J = 0
    If Chk = True Then
        For Each CFCELL In Rng
            Str1 = CFCELL.FormatConditions(I).Formula1
            Dim II As Integer
            Dim IIFlg As Boolean
            Dim Tmp
            IIFlg = False
            For II = 1 To Len(Str1)
               Tmp = Mid(Str1, II, 1)
               If ("0123456789" Like "*" & Tmp & "*") Then
                  IIFlg = True
               Else
                  If (IIFlg) Then Exit For
               End If
            Next
            Tmp = Right(Str1, Len(Str1) - II + 1)
            Str1 = "=" & CFCELL.Address & Tmp
            If bCount = False Then
                If Evaluate(Str1) = True Then J = J + 1
            Else
                If Evaluate(Str1) = True Then J = J + CFCELL
            End If
        Next CFCELL
    Else
        CountCFCells = "Color Not Found"
        Exit Function
    End If
    CountCFCells = J
End Function

[YasserKhalil]

Thanks Mr. Bruce for nice formula
In fact I know that I can achieve counting using Countif function ..but in original file the issue is very complicated so I use this UDF function

Thank you very much Mr. TMS for this addition to code but I need to deal with the UDF in normal way
That's I need to select any cell in any column without that error ..Just to display or update the results at once

[PCI]

YasserKhalil you are welcome
It was a pleasure, to understand the issue and to prepare the update