|
Conditional Formatting
I have two rows where I need to show a yellow background if the top number is
higher than the bottom. I have done a conditional format which works great until it runs into the occassional cell that contains a N/A because there is no data to report. (The cell is suppose to contain the N/A) How can I hightlight the top row and bottom row it the top row cotains the hightest number and have neither of them highlight if the top row has the lowest or a N/A. Currently conditional format is. Formatting in C6 if c6=N/A no formatting if c6 is greater than c7 yellow background Formatting in C7 if c7=N/A no formatting if C7 is greater than c6 no formatting if c7 is less than c6 yellow background (this is the problem area, if c6 equals N/A it is reading it as less than) Any suggestions? |
Sandy,
Thank you answering my question. Could you tell me what format should follow the =or(c7<c6, isna(c6))? "sweetsue516" wrote: I have two rows where I need to show a yellow background if the top number is higher than the bottom. I have done a conditional format which works great until it runs into the occassional cell that contains a N/A because there is no data to report. (The cell is suppose to contain the N/A) How can I hightlight the top row and bottom row it the top row cotains the hightest number and have neither of them highlight if the top row has the lowest or a N/A. Currently conditional format is. Formatting in C6 if c6=N/A no formatting if c6 is greater than c7 yellow background Formatting in C7 if c7=N/A no formatting if C7 is greater than c6 no formatting if c7 is less than c6 yellow background (this is the problem area, if c6 equals N/A it is reading it as less than) Any suggestions? |
Sorry I misread your conditions, will:
C6: =ISNA(C6) with no formatting =ISNA(C7) with yellow formatting =C6C7 with yellow formatting C7: =OR(ISNA(C7),C7C6) with no formatting =ISNA(C6) with yellow formatting =C7<C6 with yellow formatting do what you want? -- HTH Sandy Replace@mailinator with @tiscali.co.uk "sweetsue516" wrote in message ... Sandy, Thank you answering my question. Could you tell me what format should follow the =or(c7<c6, isna(c6))? "sweetsue516" wrote: I have two rows where I need to show a yellow background if the top number is higher than the bottom. I have done a conditional format which works great until it runs into the occassional cell that contains a N/A because there is no data to report. (The cell is suppose to contain the N/A) How can I hightlight the top row and bottom row it the top row cotains the hightest number and have neither of them highlight if the top row has the lowest or a N/A. Currently conditional format is. Formatting in C6 if c6=N/A no formatting if c6 is greater than c7 yellow background Formatting in C7 if c7=N/A no formatting if C7 is greater than c6 no formatting if c7 is less than c6 yellow background (this is the problem area, if c6 equals N/A it is reading it as less than) Any suggestions? |
Sandy,
The "N/A" is not a error message but is a value I put in the cell. So, if the cell value is "N/A" in either C6 or C7 the format for both cells should be no formatting. Does this make a difference in what you would do for this situation? The desired results are If C6C7 yellow background in both If C7C6 no formatting in either If either C7 or C6 = "N/A" no formatting in either Thank you in advance. "sweetsue516" wrote: I have two rows where I need to show a yellow background if the top number is higher than the bottom. I have done a conditional format which works great until it runs into the occassional cell that contains a N/A because there is no data to report. (The cell is suppose to contain the N/A) How can I hightlight the top row and bottom row it the top row cotains the hightest number and have neither of them highlight if the top row has the lowest or a N/A. Currently conditional format is. Formatting in C6 if c6=N/A no formatting if c6 is greater than c7 yellow background Formatting in C7 if c7=N/A no formatting if C7 is greater than c6 no formatting if c7 is less than c6 yellow background (this is the problem area, if c6 equals N/A it is reading it as less than) Any suggestions? |
Does this make a difference in what you would do for this situation?
Yes that simplifies it a lot. Both C6 & C7: =OR($C$6=""N/A",$C$7="N/A") with no format =$C$6$C$7 with yellow formating -- HTH Sandy Replace@mailinator with @tiscali.co.uk "sweetsue516" wrote in message ... Sandy, The "N/A" is not a error message but is a value I put in the cell. So, if the cell value is "N/A" in either C6 or C7 the format for both cells should be no formatting. Does this make a difference in what you would do for this situation? The desired results are If C6C7 yellow background in both If C7C6 no formatting in either If either C7 or C6 = "N/A" no formatting in either Thank you in advance. "sweetsue516" wrote: I have two rows where I need to show a yellow background if the top number is higher than the bottom. I have done a conditional format which works great until it runs into the occassional cell that contains a N/A because there is no data to report. (The cell is suppose to contain the N/A) How can I hightlight the top row and bottom row it the top row cotains the hightest number and have neither of them highlight if the top row has the lowest or a N/A. Currently conditional format is. Formatting in C6 if c6=N/A no formatting if c6 is greater than c7 yellow background Formatting in C7 if c7=N/A no formatting if C7 is greater than c6 no formatting if c7 is less than c6 yellow background (this is the problem area, if c6 equals N/A it is reading it as less than) Any suggestions? |
Sandy,
That's it!!!! Thank you! It made things a lot easier once I explained it correctly. "sweetsue516" wrote: I have two rows where I need to show a yellow background if the top number is higher than the bottom. I have done a conditional format which works great until it runs into the occassional cell that contains a N/A because there is no data to report. (The cell is suppose to contain the N/A) How can I hightlight the top row and bottom row it the top row cotains the hightest number and have neither of them highlight if the top row has the lowest or a N/A. Currently conditional format is. Formatting in C6 if c6=N/A no formatting if c6 is greater than c7 yellow background Formatting in C7 if c7=N/A no formatting if C7 is greater than c6 no formatting if c7 is less than c6 yellow background (this is the problem area, if c6 equals N/A it is reading it as less than) Any suggestions? |
"sweetsue516" wrote in message
... Sandy, That's it!!!! Thank you! It made things a lot easier once I explained it correctly. You're welcome. Just glad we got it worked out eventually :-) -- Regards Sandy Replace@mailinator with @tiscali.co.uk ? |
| All times are GMT +1. The time now is 04:34 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com