Excel - Conditional Formatting

[clamum]

Hi all,

I have a spreadsheet that I use to keep track of my finances. One worksheet is used to keep track of current bills; relevant columns are "Name", "Total Due", "Date Due", "Amount Paid", and "Date Paid".

I'd like to create a conditional formatting rule (the best way to do this I'm guessing?) that set the background cell color for the above five columns to that light red "Bad" style. I want to apply this rule if:
- "Date Due" minus today's date is less than or equal to 7 days (C2-TODAY() <= 7)
AND
- "Amount Paid" is empty (D2="")
- Apply this formatting rule to: Columns A, B, C, D, E

Seems simple but I cannot get this dang thing to work. I go to Conditional Formatting -> Manage Rules -> New Rule (for This Worksheet). Click Use Formula and type in "=AND(C2-TODAY()<=7, D2="")". Click the button for "Applies to" and click and drag to select columns A through E ($A : $E).

Hit Apply and then OK.

Tons of cells that do not fit the criteria are set to a light red background color and actually, when looking at the formula, for some reason when I did the "Applies to" criteria, it actually changed my formula I set for the actual conditional formatting rule itself.

I'm tempted to give up and just forget about it cause I've tried this so many times and it just doesn't work. Am I doing something stupid here? Any help would be greatly appreciated.

Also, I don't mind doing any scripting work if Conditional Formatting isn't the best way to do this.

 

[Merad]

Assuming that you don't want highlighting unless a date has been entered:

=AND($C2-TODAY()<=7,$D2="",NOT(ISBLANK($C2)))

 

[clamum]

Wow. I had that same formula, sans the "ISBLANK($C2)" part, and it was all crazy. I was under the impression that the "AND()" function only accepted two parameters? I did have a nested AND() before but that wasn't working out either.

Anyway, thanks a ton man, that works great!

EDIT: Ah, I think I see where it went crazy. Subtracting TODAY() from a blank date. Derp derp.