Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting while working with 2 columns
I'm working on excel 2003, and I have a list table that has six columns.
Column E contains volatile data which computes the number of days the row item has been on the table until the entry is updated again. What I've been trying to do is to shade the cell in column A, depending on the value in the corresponding cell in column E which falls along the same row. The only method I've been able to figure out so far that is guaranteed to work is to make conditional formats for every cell in column A, but that will be too tedious, and also inappropriate, since the data occasionally gets rearranged because it is frequently updated, thus it would change the cell references. Example: A | B | C | D | E | | | | 18 | | | | 7 | | | | 24 | | | | 2 condition 1: E 20 Red condition 2: E 13 Orange condition 3: E 6 Green I think i need a formula to make the conditional format refer _implicitly_ to the corresponding cell that is along the same row under column E, but i just can't figure it out. Hope you guys can help me out with this. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting while working with 2 columns
Select all the cells in column A, starting at A1
Got to FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =$E120 Click the Format button Select the Palette tab Choose Red OK Click the Add button Change Condition 2 to Formula Is Add a formula of =$E113 Click the Format button Select the Palette tab Choose Orange OK Click the Add button Change Condition 1 to Formula Is Add a formula of =$E16 Click the Format button Select the Palette tab Choose Green OK OK Also, see http://www.xldynamic.com/source/xld.CF.html#lights -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jonathan" wrote in message ... I'm working on excel 2003, and I have a list table that has six columns. Column E contains volatile data which computes the number of days the row item has been on the table until the entry is updated again. What I've been trying to do is to shade the cell in column A, depending on the value in the corresponding cell in column E which falls along the same row. The only method I've been able to figure out so far that is guaranteed to work is to make conditional formats for every cell in column A, but that will be too tedious, and also inappropriate, since the data occasionally gets rearranged because it is frequently updated, thus it would change the cell references. Example: A | B | C | D | E | | | | 18 | | | | 7 | | | | 24 | | | | 2 condition 1: E 20 Red condition 2: E 13 Orange condition 3: E 6 Green I think i need a formula to make the conditional format refer _implicitly_ to the corresponding cell that is along the same row under column E, but i just can't figure it out. Hope you guys can help me out with this. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting while working with 2 columns
Thanks, but unfortunately this wouldn't work for me. You see, the data I work
with is updated daily, so the rows change from time to time. if i choose E1 explicitly, it might not shade the cell correctly when the row number for that line item changes. "Bob Phillips" wrote: Select all the cells in column A, starting at A1 Got to FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =$E120 Click the Format button Select the Palette tab Choose Red OK Click the Add button Change Condition 2 to Formula Is Add a formula of =$E113 Click the Format button Select the Palette tab Choose Orange OK Click the Add button Change Condition 1 to Formula Is Add a formula of =$E16 Click the Format button Select the Palette tab Choose Green OK OK Also, see http://www.xldynamic.com/source/xld.CF.html#lights -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting while working with 2 columns
Shouldn't matter, the formula will update inline with the rows.
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jonathan" wrote in message ... Thanks, but unfortunately this wouldn't work for me. You see, the data I work with is updated daily, so the rows change from time to time. if i choose E1 explicitly, it might not shade the cell correctly when the row number for that line item changes. "Bob Phillips" wrote: Select all the cells in column A, starting at A1 Got to FormatConditional Formatting Change Condition 1 to Formula Is Add a formula of =$E120 Click the Format button Select the Palette tab Choose Red OK Click the Add button Change Condition 2 to Formula Is Add a formula of =$E113 Click the Format button Select the Palette tab Choose Orange OK Click the Add button Change Condition 1 to Formula Is Add a formula of =$E16 Click the Format button Select the Palette tab Choose Green OK OK Also, see http://www.xldynamic.com/source/xld.CF.html#lights -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting while working with 2 columns
I'm sorry. I doublechecked. i mistyped the formula, and it works! haha.
Thanks a lot, you're great! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting - not working | Excel Discussion (Misc queries) | |||
Conditional formatting in VBA not working | Excel Discussion (Misc queries) | |||
Conditional Formatting ROW not working | Excel Discussion (Misc queries) | |||
Why isn't the conditional formatting working? | Excel Programming | |||
Conditional formatting not working! | Excel Programming |