Excel VBA not counting colored cells

[belblanco]

I have a spreadsheet with a column that is light yellow in color. Base
on criteria, I use conditonal formatting to color some cells red i
that column. I eventually want to count those red cells, but the colo
index shows that they are still light yellow and not red. I use th
following UDF to display the index number. Does anyone know why thi
is, or is there another technique I should be using? Thanks
belblanco

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function (UDF) returns the ColorIndex value of the Interior
' (background) of a cell, or, if OfText is true, of the Font in th
cell.
' ex: =CELLCOLORINDEX(C32,FALSE)
'
Application.Volatile True

If OfText = True Then
CellColorIndex = InRange(1, 1).Font.ColorIndex
Else
CellColorIndex = InRange(1, 1).Interior.ColorIndex
End If

End Functio

 

[Frank Kabel]

Hi
the problem is taht conditional format does NOT change the
colorindex property. This property will ALWAYS show the
default color index.

Though Bob Phillips / Harlan Grove posted some months ago
a solution which evaluates conditional formats directly (a
complicated thing) I would suggest not to count the colors
BUT to check the conditions directly. e.g. check if the
values met your condition.

 

[belblanco]

Thanks Frank,

I'll take your advice.

belblanc