Excel VBA not counting colored cells

  • Thread starter Thread starter belblanco
  • Start date Start date
B

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
 
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.
 
Back
Top