Conditional Formatting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nine - 23 Oct 2006 00:52 GMT

I am having an issue with conditional formatting. I want traffic lights to
be displayed, which I can do no problem, however I cannot get them to change
color properly.

I have a column of negative numbers which the green light should be
displayed if the difference between cell A2 and B2 is 9 to 11, Yellow if the
difference is 8 or 12, and Red if 7 or less, or 13 or more.

Is this possible?

Thanks
Dave

Reply to this Message

Ragdyer - 23 Oct 2006 02:02 GMT

You said *a* column is negative!

Which one ... A or B?

And, is the difference supposed to be positive, or negative, or doesn't it
matter?

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>
> I am having an issue with conditional formatting. I want traffic lights to
[quoted text clipped - 9 lines]
> Thanks
> Dave

Reply to this Message

nine - 23 Oct 2006 23:09 GMT

You are correct with the question, I did not explain correctly. All data in
1 column "A" and the data is supposed to vary from 1 row to the next by 10.

Example
-60
-70
-60
-70

This should return green lights in the next column "B"

Thanks
Dave

> You said *a* column is negative!
>
[quoted text clipped - 18 lines]
>> Thanks
>> Dave

Reply to this Message

Paul H - 23 Oct 2006 23:54 GMT

I solved it this way. I created an array called colours like this:
1 Red
2 Red
3 Red
4 Red
5 Red
6 Red
7 Red
8 Yellow
9 Green
10 Green
11 Green
12 Yellow
13 Red

Then in the conditional formatting box put in as formula
=VLOOKUP(ABS(B2-A2),colours,2)="Green", changing the Green bit for the
appropriate colour. (I think you can put in A2-B2 instead and it doesn't
seem to matter, just looks neater).
Paul H

> You are correct with the question, I did not explain correctly. All data
> in 1 column "A" and the data is supposed to vary from 1 row to the next by
[quoted text clipped - 34 lines]
>>> Thanks
>>> Dave

Reply to this Message

nine - 24 Oct 2006 02:53 GMT

Worked great thanks for the help!

Dave

>I solved it this way. I created an array called colours like this:
> 1 Red
[quoted text clipped - 55 lines]
>>>> Thanks
>>>> Dave

Reply to this Message