Excel VBA not counting colored cells
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA not counting colored cells
 

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.


-----Original Message-----
I have a spreadsheet with a column that is light yellow
in color. Based
on criteria, I use conditonal formatting to color some
cells red in
that column. I eventually want to count those red cells,
but the color
index shows that they are still light yellow and not
red. I use the
following UDF to display the index number. Does anyone
know why this
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 the
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 Function


---
Message posted from http://www.ExcelForum.com/

.

Excel VBA not counting colored cells
 

Thanks Frank,

I'll take your advice.

belblanc

--
Message posted from http://www.ExcelForum.com