Problem with my color count

[andrewrgd]

I am using a validation list box to select a name. The names of the list are defined as =offset(sheet1!$A$5,0,0,CountA(Sheet1!$A:$A),1)

However, if I update the names on the list it affects my color count function formula =countbycolor($D$8:$M$33,15,FALSE). The cell where I am resulting the number of colored cells shows #VALUE! If I refresh the formula again the result cell shows the correct result.

Any ideas on how to resolve would be appreciated. Below is the code I'm using for the color count:

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function