formulas for conditional formatting

[Maliya9]

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(A2,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??

 

[Bob Phillips]

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)

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

(For green)

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

 

[okrob]

You forgot the R in Right for the red formula.
=AND(OR(LEFT(B2,1)<LEFT(A2,1),AND(LEFT(B2,1)<LEFT(A2,1),RIGHT(B2,1)>IGHT(A2,1))),B2<>"",A2<>"")

SHOULD BE:
=AND(OR(LEFT(B2,1)<LEFT(A2,1),AND(LEFT(B2,1)<LEFT(A2,1),RIGHT(B2,1)>RIGHT(A2,1))),B2<>"",A2<>"")


Rob

 

[Maliya9]

Thank you okrob,

However that was jsut an error when I cut and pasted it into here. I have
got the R there and it still doesn't work.

It is recognising that 3a is higher than 3b and colouring it green. But
given 1a - 1c it colours the 1c amber instead of red. I have told Excel my
data sort order, but that doesn't make any difference either.

Any ideas??

 

[Bob Phillips]

See my response.

--
HTH

Bob Phillips

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

 

[okrob]

I made my test workbook look like yours, and I came up with the same
issue...
I suggest using Bob's answer its right on...
Rob