Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting Based of Cells Based on Data Entry in anoth | Excel Discussion (Misc queries) | |||
Conditional Formatting Based on two cells in a row | Excel Worksheet Functions | |||
Conditional Formatting based on other cells | Excel Worksheet Functions | |||
Conditional Formatting Multiple cells based on 2 cells | Excel Worksheet Functions | |||
To set the whole row in a particular colo. (based on some conditio | Excel Discussion (Misc queries) |