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!

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)

"Jonathan" <(E-Mail Removed)> wrote in message
newsCD4EE28-07E6-43B8-8F24-(E-Mail Removed)...
> 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!

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

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" <(E-Mail Removed)> wrote in message
news:21D73D16-BAD4-4CB4-96C8-(E-Mail Removed)...
> 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 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)
>

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