conditional formatting based on different cells with more conditio
I'm trying to get conditional formatting in the cells in one column. I want
them to be blue if the cell in front of it contains the value "N/A", I want it to be green if the cell in front of it contains a value greater than 180, i want it to be yellow if the cell in front of it is greater than 180 AND if the months in the two cells before THAT one are 6 months apart. ex: A5= 12/20/2007; B5= 7/25/2008; C5= 218 (formula of B5-A5); D5 has a conditional formula to produce the answer "No" because C5 is greater than 180. But I need that to automatically shade green. BUT...if A6=12/19/2007 and B6= 6/25/2008, C6 will be 189. The formula in D6 produces a "No" but I need it to be yellow because even though it is greater than 180, the month in A6 (December) is 6 months away from the month in B6 (june). Is this even possible? |
conditional formatting based on different cells with more conditio
You get 3 levels of conditional formatting, so this should work:
Condition1: Formula Is: =D5="N/A" Format: Blue background Condition2: Formula Is: =(IF(YEAR(A5)=YEAR(B5),MONTH(B5)-MONTH(A5), MONTH(B5)+12-MONTH(A5)))<=6 Format: Yellow background Condition3: Formula Is: =(IF(YEAR(A5)=YEAR(B5),MONTH(B5)-MONTH(A5), MONTH(B5)+12-MONTH(A5)))6 Format: Green background -- "Actually, I *am* a rocket scientist." -- JB Your feedback is appreciated, click YES if this post helped you. "sarajane82" wrote: I'm trying to get conditional formatting in the cells in one column. I want them to be blue if the cell in front of it contains the value "N/A", I want it to be green if the cell in front of it contains a value greater than 180, i want it to be yellow if the cell in front of it is greater than 180 AND if the months in the two cells before THAT one are 6 months apart. ex: A5= 12/20/2007; B5= 7/25/2008; C5= 218 (formula of B5-A5); D5 has a conditional formula to produce the answer "No" because C5 is greater than 180. But I need that to automatically shade green. BUT...if A6=12/19/2007 and B6= 6/25/2008, C6 will be 189. The formula in D6 produces a "No" but I need it to be yellow because even though it is greater than 180, the month in A6 (December) is 6 months away from the month in B6 (june). Is this even possible? |
All times are GMT +1. The time now is 10:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com