Complicated Excel Conditional Formatting 1

[jdegeorge]

Hi

I have a spreadsheet with simple conditional formatting to change the color of a cell (A1: Target Date) if that cell's date value is equal to today or earlier:

Condition 1: Cell Value Is between TODAY() and 1

There is a 2nd cell (A2: Closed Date) that I want to bring into this condition for A1 if possible so that the original condition is true ONLY if A2 is BLANK. I need a 2-part condition because Excel applies only the first condition that is comes across that's true.

This is what I tried:

Condition 1: Cell Value Is between =IF(ISBLANK(A2),TODAY(),A2) and 1

It doesn't work. Any ideas? Can something like this even be done?

Thanks in advance!

Jim DeGeorge

 

[xlhelp]

Condition1

Formula is
=A2=""

Condition 2 for the same cell
Cell Value is between today() and 1

 

[jdegeorge]

I already had a condition 2, so I added your "condition 1" as condition 3. Should have mentioned that. Sorry!

Here's everything:

Condition 1: If A1 <= Today and A2 is blank  (MAKE RED)
Condition 2: If A1 <= Today and A2 is not blank (MAKE YELLOW)
Condition 3: If A1 >= Today+3 regardless of A2 value (MAKE BLUE)

Thanks!

Jim DeGeorge

 

[xlhelp]

Thachanges everything

Condition1
Formula is
=AND(A7<=TODAY(),B7="")

Condition2
Formula is
=AND(A7<=TODAY(),B7<>"")

Condition 3
Formula is
=A7>TODAY()

Please make sure it says Formula is and not Cell Value is

 

[xlhelp]

Sorry, I was working with Cell A7 as my date

 

[jdegeorge]

Wonderful! This works fantastic! Thanks and have a great weekend. And, enjoy the star!

Jim DeGeorge

 

[jdegeorge]

Oops! I just copied this condition to all of the related cells. When I got to a section of the spreadsheet where both A1 and A2 were blank, Condition 1 kicked in and the cell was colored in.

So, I really have 4 conditions, with the last one being:

A1 is blank...do nothing.

Can that be worked in?

Jim DeGeorge

 

[xlhelp]

Change condition 1 to

=AND(A7<=TODAY(),A7>1,B7="")

 

[jdegeorge]

Bingo! I wish I could give you another star. Excel is definitely not my forte.

Jim DeGeorge