Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior color
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior color
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...tm#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. --- HTH, David McRitchie, Microsoft MVP - Excel My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Interior color
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). -- Regards, Tom Ogilvy " wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell background color (interior color) setting not working | Excel Programming | |||
Transparency for interior color? | Excel Programming | |||
Interior color | Excel Programming | |||
Interior color | Excel Programming | |||
Passing Back Color to Interior Color | Excel Programming |