Interior color

[glen.e.mettler]

using 2003
I have a workbook that color codes numeric data based on user
parameters (red, yellow, green)
in an adjacent column I track the trend (up, down, no change -
depicted with and up arrow, down arrow or horizontal arrow). The
current data is compared to the previous period and displays the
appropriate arrow.

I use conditional formatting to color code the current data but I have
a macro that color codes the arrows (I couldn't get conditional
formatting to work)

If the trend is up and bad, I want the interior color to be red. If
the trend is up and good, I want the interior color to be green.
Likewise for the trend being down.

If the trend is not changed (horizontal arrow), I want the interior
color to be the interior color of the current data be it red, yellow,
green. The problem is this. It doesn't change the color the "no
change" condition.

When I step thru the code, I get 35 as the interior color although I
can clearly see that the current condition is red (3) or yellow (6) or
green (4).

Here is the code:

For r = 4 To 15
'if arrow (g) is horizonal - no change
If Cells(r, 9) = "g" Then
Range("g" & r).Select
Prevcolor = Selection.Interior.ColorIndex
Range("I" & r).Interior.ColorIndex = Prevcolor
End If
'if arrow is up (h) and trend is bad (R)
If Cells(r, 9) = "h" And Cells(r, 10) = "R" Then
Range("I" & r).Interior.ColorIndex = red
End If
'if arrow is up (h) and trend is good (G)
If Cells(r, 9) = "h" And Cells(r, 10) = "G" Then
Range("I" & r).Interior.ColorIndex = green
End If
'if arrow is down (i) and trend is bad (R)
If Cells(r, 9) = "i" And Cells(r, 10) = "R" Then
Range("I" & r).Interior.ColorIndex = red
End If
'if arrow is down (i) and trend is good (G)
If Cells(r, 9) = "i" And Cells(r, 10) = "G" Then
Range("I" & r).Interior.ColorIndex = green
End If
Next r

I am confused.

Glen

 

[David McRitchie]

In VBA lettercase makes a difference in a comparison,
so without really looking at your code, I think that is the
problem.
if ucase(Cells(r, 9)) = "G" then
if lcase(Cells(r, 9)) = "g" then
if InStr(1, Cells(r, 9), "g", vbTextCompare) then

http://www.mvps.org/dmcritchie/excel/strings.htm#sensitivity

If a value is manually entered you might consider
using a change event macro.
http://www.mvps.org/dmcritchie/excel/event.htm#change
be sure to start reading at the top of the page though.

 

[Guest]

Read this article to clear up your confusion:
http://www.cpearson.com/excel/CFColors.htm

Whether conditional formatting is changing the color or not can not be read
by examing any properties. Your best bet is to actually make the
determination in the code as to what the condition is in the cell (compare
its value to the previous cell or whatever condition you need to check to
determine the trend).