Conditional Formatting while working with 2 columns

[Guest]

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!

 

[Bob Phillips]

Select all the cells in column A, starting at A1

Got to Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =$E1>20
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 =$E1>13
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 =$E1>6
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)

 

[Guest]

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]

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)

 

[Guest]

I'm sorry. I doublechecked. i mistyped the formula, and it works! haha.
Thanks a lot, you're great!