Complex IF, NOT, AND statement with Conditional Formatting

[lifeonathread]

Ok... you all are probably going to think I'm crazy....

What I'm trying to do is set up a nested IF, NOT, AND statement with conditional cell formatting. I already used the Menu cell formatting for a different purpose and because I can't put in more than three conditions, I need to manually put in the conditional formatting with a formula.

In Layman's terms I'm trying to make it say the following:

IF Cell A1 does NOT equal "No" AND the DATEDIF between A2 and TODAY > 30 days, then Color row A blue.

This is what I have for the formula so far:
=IF(W2=(NOT(W2="No"))AND((DATEDIF (R2, TODAY)>30)),=COLOR A2:AZ2=(155,10,0))

 

[Scott Huish]

You can't apply formatting to a cell with a formula. You will either need to use VBA or upgrade to Excel 2007 or higher which does not have the 3 conditonal format limit.

 

[lifeonathread]

I'm running Excel 2008... I just upgraded today. Sorry! I forgot to mention that.

Also, correction to the original post, the menu-driven Conditional Formatting is already in use... not cell formatting.

 

[Scott Huish]

I'm guessing that's a MAC version. On the PC, in 2007, they eliminated the 3 format limit. You might want to try that first.

 

[lifeonathread]

yes.. you are correct I am running on a mac.. and such can't swap to Excel 2007 since I don't have windows. It SHOULD have the same features as Excel 2007, though the conditional formatting still appears to be limited to 3 conditions.

 

[Scott Huish]

Sorry, don't have a MAC.

 

[sr12345]

I'm not sure about the Mac version, but on a PC, here's how you would do it.

First of all, ditch the IF. Conditional formatting equations should boil down to "=TRUE" (which means that you apply the different format), or "=FALSE" (in which case you don't).

=AND(not(a2="NO"),a2-TODAY()>30). Then define that format to be blue.

 

[Scott Huish]

or more correctly TRUE or not TRUE

 

[lifeonathread]

Thanks Sr12345, I'll give that a try!

 

[Flue]

Maybe this;

=AND((A1="yes"),DATEDIF(B1,NOW(),"d")>30)