Interior color

G

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
 
D

David McRitchie

T

Tom Ogilvy

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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top