Help with the following code

[Guest]

I have cells that are set with conditional formatting to be either red,
yellow, or green. I am trying to loop through the cells, and when the cell
color is red I need to increment the "intOutOfLimit counter". The problem I
am having is everything I have tried, including the code below, when the
statement "ColorIndex <> 10" is executed it returns a value of -4142 for each
cell no matter what the color. Does anyone have any suggestions.
Thanks,
Vince

Do Until rw = 22
For i = 2 To 26
If Sheets("PM").Cells(rw, cl).Interior.ColorIndex <> 10 Then
intOutOfLimit = intOutOfLimit + 1
End If
cl = cl + 1
Next
cl = 2
rw = rw + 1
Loop

 

[Bob Phillips]

CF doesn't set the colorindex. To test what colour a cell is with CF, you
have to test whether it matches the condition within CF.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Norman Jones]

Hi Vince,

Try using the conditional formatting condition as the variable increment
trigger.

 

[Guest]

There are 15 rows by 25 columns. Each row has a different CF that applies to
all of the columns in that row. I think you are saying to check the value of
the cell against the CF condition, but I would have to code the CF condition,
or try to put it in a table of cells somewhere wouldn't I? I there not a way
in VB to check the CF condition, what state it is, or what color it is?
For example row 10 CF is this: Between .49 and .54 = Green, < .49 = Yellow,
and >.54 = Red.

Thanks for your help
Vince

 

[Tom Ogilvy]

No there isn't a way to do that directly.

Chip Pearson illustrates one approach:

http://www.cpearson.com/excel/CFColors.htm

 

[Guest]

Tom,
Thanks for the link, now could I bother you for one more bit of advice. The
discription of the code below is exactly what I want to do. I want to "sum
the cells that have a specified background color applied by conditional
formatting". My question is how do I make the call to this function? The
range of the cells in my worksheet is "B7:Z21" and the color I want to sum on
is anything other than green. Reading the info at the link you provided it
looks like I would not need the "GetStripped" code but I am not sure of that.
I would appreciate any feedback or suggestions you could provide. What I am
trying to do is calculate what percentage of cells in the given range are not
green, or out of spec.

Thanks for you help
Vince

Function SumByCFColorIndex(Rng As Range, CI As Integer) As Double
Dim R As Range
Dim Total As Double
For Each R In Rng.Cells
If ColorIndexOfCF(R, False) = CI Then
Total = Total + R.Value
End If
Next R
SumByCFColorIndex = Total
End Function

 

[Guest]

I found the correct way to make the call. Thanks for the link it was a great
help!

Vince