Excel 2007 : Excel 2007 - Conditional Formatting entire row based on date in a cell

**Walk6860** · 09-29-2011, 04:23 PM

Hi all,

For whatever reason, I cannot produce a funtional formula to do the following:

I have a set of dates running downcolumn G, spanning within a 3 month period (they are delivery dates).
I would like to conditionally format so that the entire row is highlighted one colour if the date is 3weeks away, another colour if 2 weeks away, and yet another colour if a week away.

Please help!

Thanks

**tlafferty** · 09-29-2011, 04:40 PM

Select the first range of data containing a date, but don't do the whole row, it wastes memory and artificially bloats your book, just select a relevant record. Launch conditional formatting, then choose new rule, use a formula:

Please Login or Register  to view this content.

this will give you dates within a week. Make it +14 for 2 weeks, etc.

**Walk6860** · 09-30-2011, 08:47 AM

Hi Thomas,

Thank you.
I should also note, some cells in column G contain text rather than numbers.
When i enter in the formula you specify below, it highlights all the text as well.

How can this be overcome?

**romperstomper** · 09-30-2011, 08:49 AM

You can use:
=And(IsNumber($G1),TODAY()+7<=$G1)

**Walk6860** · 09-30-2011, 09:39 AM

Thanks.

This seems to highlight more cells than actually required.
Does it pick up any dates that are greater than or equal to 7?

I am trying to flag deliveries that are 1 week out, 2 weeks out and 3 weeks out all consecutively and I want the entire row to be highlighted a different colour based on which of the aforementioned delivery weeks they fall under.

**GeneralDisarray** · 09-30-2011, 10:35 AM

Hello, try this out (seemed easier to attach the example than to explain the rules in detail)

But, i made the following rules:

Due today / within 1 week / within 2 weeks / within 3 weeks / greater than 3 weeks out...

seem to work ok.

----

if you like / dislike my post please rate it by clicking on the "reputation" scales at the upper right hand corner of this post