Try this.

First create a name constant, Ctrl-F3, with a name of codes and a RefersTo
value of
={"1c","1b","1a","2c","2b","2a","3c","3b","3a","4c ","4b","4a","5c","5b","5a"
,"6c","6b","6a"}

Then use these three formulae

=MATCH(B2,codes,0)-MATCH(A2,codes,0)1

=MATCH(B2,codes,0)-MATCH(A2,codes,0)=1

=MATCH(B2,codes,0)-MATCH(A2,codes,0)<=0

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Maliya9" wrote in message
...
I want Excel to background colour a cell, Red, Amber or Green using
conditional formatting dependent upon
the data in it compared to the data in the cell immediately left of it.

e.g. if the data is equal, or lower value = red
if the data is higher by 1 = amber
if the data is higher by 2+ = green

My data is numerical and with letters running:

1c,1b,1a,2c,2b,2a,3c,3b,3a,4c,4b,4a,5c,5b,5a,6c,6b ,6a
where 6a is highest and 1c is lowest

I have the formulas:


=AND(OR(LEFT(B2,1)LEFT(A2,1),AND(LEFT(B2,1)=LEFT (A2,1),RIGHT(B2,1)<RIGHT(A
2,1))),B2<"",A2<"")

(For green)



=AND(OR(LEFT(B2,1)=LEFT(A2,1),AND(LEFT(B2,1)=LEFT( A2,1),RIGHT(B2,1)=RIGHT(A2
,1))),B2<"",A2<"")

(for amber)


=AND(OR(LEFT(B2,1)<LEFT(A2,1),AND(LEFT(B2,1)<LEFT( A2,1),RIGHT(B2,1)IGHT(A2,
1))),B2<"",A2<"")

( for red) the green and amber formulas work whatever data is entered, but
the red formula is only working if the number is different and isn't
recognizing that 1c is lower than 1b (for eg)

Please can anyone help??