ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   formulas for conditional formatting (https://www.excelbanter.com/excel-programming/381589-formulas-conditional-formatting.html)

Maliya9
formulas for conditional formatting
 
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 isnt
recognizing that 1c is lower than 1b (for eg)

Please can anyone help??

Bob Phillips
formulas for conditional formatting
 
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??

okrob
formulas for conditional formatting
 
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 wrote:
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??

Maliya9
formulas for conditional formatting
 
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??

"okrob" wrote:

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 wrote:
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
formulas for conditional formatting
 
See my response.

--
HTH

Bob Phillips

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

"Maliya9" wrote in message
...
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??

"okrob" wrote:

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 wrote:
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??

okrob
formulas for conditional formatting
 
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


Bob Phillips wrote:
See my response.

--
HTH

Bob Phillips

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

"Maliya9" wrote in message
...
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??

"okrob" wrote:

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 wrote:
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??


All times are GMT +1. The time now is 08:47 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com