Excel 2007 : Dates and Conditional Formatting

Alright before anyone starts bashing my head in, yes I know there are countless threads out there for conditional formatting... but I can't seem to apply any of it to my situation.

Attached is an example book with my rows/columns.

I'm trying to format the entire row for each individual based on a few criteria:

If C/O (Closeout) is within 63 days of TODAY, whole row should turn yellow, bold text.
If C/O (closeout) is YESTERDAY, whole row should turn red, bold text
If Comments says "complete", turn entire row green, regardless of other formatting.

The main problem i'm running into here, is creating all 3 criteria for a single row, then replicating it to apply to the rest of the sheet.... how do i do this so that i don't have to type these 3 criteria in a zillion times?

however, what do i do if, say i have row X turn red b/c it is overdue (pas the suspense for closeout), but want to add modifiers to it?

For example, row X turns yellow, meaning it is within 63 days of closeout, but i want to show that it is on track for completion, and has met its milestone suspenses, so i write "XYZ" in the "Comments" column... how do i make the "XYZ" column turn green, showing that "XYZ" was met, while still showing the rest of the row yellow, meaning that the closeout is still within 63 days?

that being said, when it meets its next milestone suspense, "ZZZ", how do i make it to where, upon typing "ZZZ" in the "comments" column, both "XYZ" and "ZZZ" will be green? showing that both suspenses were met, and have the row still be yellow otherwise, since it is still within 63 days of closeout?

Re: conditional formatting: Copy/Paste

--------------------------------------------------------------------------------

sorry for the bland reply. I got it to work, but did them individually, as i could not figure out how to combine the rules. I was worried that with all my conditions, i'd have to type them all up on each sheet... I have approximately 20 conditions for formatting per sheet... and i have 12 sheets... that's a lot of copy/paste. Here's what i found:

On sheet 1, do all of your conditional formatting (in my case, 20 rules).
Then, select ALL cells that have been conditionally formatted.
Ensure you are on the "Home" tab of the Ribbon.
Just below the huge glowing circle, there is a button called "Format Painter"; click that.
Go to sheet 2.
Select all cells that will need the same formatting.
DONE. Once these cells are selected, it copys/pastes the conditional formatting from sheet 1 to them.

NOTE: The layout of Sheet 1 and Sheet 2 will need to be identical for this to work properly, meaning you have all the same column/row headings in the same order (or your information will be mis-ruled).

Hope this helps others!