Condittional Formatting: Range of days

**Kinna** · 05-29-2009, 08:29 AM

Hi, I need help to come up with the following conditional formatting:

Color cell AB3 in red if B3 shows the word OPEN and the date on AA is -1 day, -2 days, 0 day, 1day, 2 days... 50days from today (a range from -2 days to +50 days from the current date)

Could anyone kindly help me? I quite confused trying to figure this out

**NBVC** · 05-29-2009, 08:34 AM

Try Conditonal Format Formula:

Select range and go to Format|Conditional Formatting

Select Formula Is and enter formula

=AND($B2="OPEN",$AA2>=TODAY()-2,$AA2<=TODAY()+50)

Click Format to choose colour

**Kinna** · 05-29-2009, 08:59 AM

Since I am using XP and I have lots of conditionals to make on the same cell is there anyway I can add this other formula ($Y3>=$AA3) to the one you have mentioned?

I have tryied this:

Please Login or Register  to view this content.

But it annuls the previous formula... is there anyway I can make the formula ($Y3>=$AA3)"less important" than the rest of the formula?

**NBVC** · 05-29-2009, 09:02 AM

Not sure what you mean by making it less important...

Maybe you can spell out exactly what you want to have happen?

Maybe an OR()

=OR(AND($B3="OPEN",$AA3>=TODAY()-2,$AA3<=TODAY()+200),$Y3>$AA3)

This will colour if either the first 3 conditions are true OR the added condition is true....