count color VB and conditional format

Question

Hello,

Can I modify this VB to update when a cell's color is changed to match? It works but I have to select A14 and hit enter.

A13 = color to be counted

A14 =CountCcolor($B$9:$DD$23,A13)

Function CountCcolor(range_data As Range, criteria As Range) As Long

    Dim datax As Range

    Dim xcolor As Long

xcolor = criteria.Interior.ColorIndex

For Each datax In range_data

    If datax.Interior.ColorIndex = xcolor Then

        CountCcolor = CountCcolor + 1

    End If

Next datax

End Function

Can this be modified to count cells that have been conditionally formatted for color

=ISNUMBER(B9) color cells purple

=$B$9:$DB$23

Thanks

Answer 1

Count numerics only in a cell.

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},"")))Gord

Answer 2

Application.Volatile works.

Is there a count funtion for counting just numbers in a cell without text?  I've searched.  I have cells with numbers and text that I don't want counted.

Thanks

Answer 3

If you add the line

    Application.Volatile

at the beginning of the function code, Excel will recalculate the function whenever it performs any calculation. Simply changing a color won't trigger a recalculation - Excel doesn't work that way.

If you want to count conditionally formatted cells, it's better to count based on the condition behind the rule, e.g.

=COUNT($B$9:$DB$23)

counts the number of cells with a number value in the range.