| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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?? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional formatting using formulas | Excel Discussion (Misc queries) | |||
| Conditional Formatting-No Formulas | Excel Worksheet Functions | |||
| Conditional formatting formulas | Excel Discussion (Misc queries) | |||
| Conditional Formatting for Formulas | Excel Discussion (Misc queries) | |||
| Help Using Formulas in Conditional Formatting | Excel Discussion (Misc queries) | |||
Hybrid Mode
