VBA Function Not Working

[Ken Hudson]

I had previously used the following function to count the number of cells with a certain interior cell color. It does not seem to work now.
Can someone tell me what the problem might be?

Formula in cell A11:

=CountRedBYCOLOR(A1:A10,3,TRUE)

Function CountRedByColor(InRange As Range, WhatColorIndex As Integer, Optional OfText As Boolean = False) As Double

Dim Rng As Range
Dim OK As Boolean
Dim RedCt As Integer
RedCt = 0

Application.Volatile True
For Each Rng In InRange.Cells
    If OfText = True Then
        If Rng.Interior.ColorIndex = WhatColorIndex Then
            RedCt = RedCt + 1
        End If
    End If
Next Rng
CountRedByColor = RedCt

End Function

 

[JoeMo]

Can you be more specific as to what you mean by " ... does not seem to work now"?

 

[Ken Hudson]

Thanks for the reply.
I realized that the function will work as long as I change a value in one of the cells.
If I change only the background color, the function is not called unless/until I either change a value in the cell or hit enter in the formula bar.
Does that make sense?
Assuming there is no fix for this, I assume that I then need to write code in a module with an "update colors" button?

 

[JoeMo]

A UDF will calculate only when the sheet is calculated or you select the cell it is in, press the F2 key and then press enter. Changing the color fill in a cell does not cause a calculation.