Formating a row based on one cell's date
 

Hello,

I'm working on a spreadsheet that has columns A - G. Column A has the dates. I would like to create a format that will highlight the entire row if column A has a date within the past 7 days. In my example for of the rows should be highlighted. I was able to find formulas that only worked on one cell. Can someone help me with this? PLEASE & THANK YOU!


**Example**
7/17/12 DEA MA ACCT CONT Closed
7/13/12 HHI MA ACCT CONT Closed
7/10/12 MMG MA ACCT CONT Closed
7/16/12 ABC P ACCT CONT Closed
7/13/12 TAL W ACCT CONT Closed
7/20/12 HOK P ACCT CONT Closed

Hi,

Is this what you mean?

You use conditional formatting and lock the column using $ in the formula.

Have a look at the conditional formatting rule to see what I mean.

Thanks for the response but I'm looking for one rule instead of two.

That is one rule... but with an AND statement...

You could use =$A1=TODAY()-6 but that will also highlight rows with dates in the future.

I made a change to the "applies to" field now everything works perfectly....I have more last question. If I wanted to use the same rule but only highlight rows that had a status other than 'CLOSED' how would I go about it?

Amend the same formula, to include it. You'd need to use AND for that.

=AND($A1=today()-6,F1<"Closed")

Change F to whichever column your "Closed" is recorded in if not F.

Hope that helps.

When using =AND($A1=today()-6,F1<"Closed"), the format changes...see attached.

File attached

Sorry, my bad. Put a $ before the F too...

The current formula is highlighting 7 days before and after today. Is there anyway to highlight only 7 days before?

Just add another condition to the AND....

=AND($A1=TODAY()-6,$A1<=TODAY(),$F1<"Closed")

Thanks for your help!

Not a problem. Happy to help.