Highlighting cells

[HaroldDavis]

Hi,

I have 2 columns with dates. I want to highlight a third column if there is less than 21 days between the two dates (e.g. below).

<TABLE style="WIDTH: 175pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=233><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc 0.5pt solid; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>Apr 26, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc 0.5pt solid; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl67 width=82>Jun 23, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc 0.5pt solid; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>Apr 26, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl67 width=82>Jun 23, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>May 3, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl67 width=82>Jun 23, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>May 10, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl67 width=82>Jun 23, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>May 17, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl67 width=82>Jun 23, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>May 10, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl67 width=82>Sep 13, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>May 17, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl67 width=82>Sep 13, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>Apr 29, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 width=82 align=right>Oct 20, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc 0.5pt solid; BACKGROUND-COLOR: #eeeeee; WIDTH: 65pt; HEIGHT: 12.75pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl66 height=17 width=87 align=right>May 31, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 62pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl67 width=82>Apr 5, 2011</TD><TD style="BORDER-BOTTOM: #cccccc 0.5pt solid; BORDER-LEFT: #cccccc; BACKGROUND-COLOR: #eeeeee; WIDTH: 48pt; BORDER-TOP: #cccccc; BORDER-RIGHT: #cccccc 0.5pt solid" class=xl65 width=64>shipped</TD></TR></TBODY></TABLE>

Thanks.

 

[HalfAce]

You should be able to use Conditional Formatting for that.
While in your third column, hit the menu for Format > Conditional Formatting.
For the Condition, choose Formula Is
For the formula, use (something like) =B1-A1<21 and then format to suit.
Use the Format Painter to copy the formatting to the rest of your range of interest.

(At least that's how you can do it in xl'03)
Hope it helps

 

[HaroldDavis]

Thanks, that's the first thing I tried but it didn't work. All of the cells ended up being highlighted. I'm using Excel 07.

 

[HalfAce]

Hmmm, it's working for me here. I don't know why it shouldn't work with 07.

 

[HaroldDavis]

Thank you. I finally got it to work. There was some glitch in the way the numbers were set up that made it not work originally.