Conditional Formatting

[nine]

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

 

[Ragdyer]

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?

 

[nine]

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

 

[Paul H]

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

 

[nine]

Worked great thanks for the help!

Dave

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