# Solved Conditional Formatting with AND OR

July 21, 2011 at 01:58:57
Specs: Windows XP
 I have 4 conditional formatting statements that I need to combine and make in to 1 if this is possible.=AND(TODAY()>=\$C\$8,TODAY()<=\$D\$8)=AND(TODAY()>=\$J\$8,TODAY()<=\$K\$8)=AND(TODAY()>=\$Q\$8,TODAY()<=\$R\$8)=AND(TODAY()>=\$X\$8,TODAY()<=\$Y\$8)What this does is highlight B8 if todays date is between any of the dates that are in the other cells in each of the statements. My problem is the company is useing 2003 and I can only put 3 CFs in a cell. I was thinking I could use an AND/OR statement but for the life of me I cannot think of how to right it.

See More: Conditional Formatting with AND OR

#1
July 21, 2011 at 06:44:44
 Not sure this is what you want, but try a Defined NameTake your four AND() formulas and tuck them away in apart of your sheet that no one is going to bother, like column XAOnce you have all four formulas in XA(1) - XA(4)Hightlight all four cellsNext, Click on the Name Box, which is is directly above the "A1" cell. Now, type the new name for the cell(s), for example: MyDateListPress Enter.You can now use the Defined Name, MyDateList in your Conditional Formatting.If I understand you correctly, all four conditons must be TRUEso simply putting: =MyDateList as the formula should work.MIKEhttp://www.skeptic.com/

Report •

#2
July 22, 2011 at 11:14:50
 so what you are saying is if any 1 of your conditional and statements is true then apply the formatting. If that is the case you are bang on with you wanting to use AND/OR statements. You can combine the whole thing into one line.=OR(AND(TODAY()>=\$C\$8,TODAY()<=\$D\$8),AND(TODAY()>=\$J\$8,TODAY()<=\$K\$8),AND(TODAY()>=\$Q\$8,TODAY()<=\$R\$8),AND(TODAY()>=\$X\$8,TODAY()<=\$Y\$8))Start with your OR statement then place each one of your AND statement separate by a comma. And if you are like me, dont forget to close with a )So in summary, if any one of your AND conditions is true, the whole OR statement is true and the formating will be applied. This conditional formating is applied in cell you want to get highlighted. if you are going to copy the conditional formating be aware of your \$ usage.

Report •

#3
July 23, 2011 at 00:02:03
 Thank you very much this is what I needed. My mistake was putting the AND first then the or and not even putting the AND in the other locations. Thank you for putting me straight again.

Report •

Related Solutions