How to change data in a cell, based on the date

[Rangerbob86]

Hey. Heres what I want to do:

I have a worksheet that has dates of when I need to do things, kind of
like a calender. Is there a way to change the color of the text when
the date approaches? Please help me!

 

[SteveG]

Rangerbob86,

You can use conditional formatting. Select a cell where your dates are
say
A1 then click on the Format menu. Select Conditional Formatting.
Change the Cell value is option to Formula is: For Condition one try
this if your date is less than 5 days from today.

=(A1-TODAY())<5

Select Format>Patterns and select your color. Click OK and OK. Copy
the format to the other cells in your range.

HTH

Steve

 

[Rangerbob86]

hey thanks! that worked. Now, is there also a way to get the formatting
to go away, once the date has passed?

 

[SteveG]

This should work.

=AND((A1-TODAY())<5,(A1-TODAY())>=0)

Cheers,

Steve

 

[Rangerbob86]

great! so that works....but is there a way to copy and paste the
conditional formatting to each of my cells that contain dates? because
i think in your formula you have to change A1 to whatever cell it is,
right? so do I have to do each one manually?

 

[SteveG]

You can use the Format Painter. Select the first cell in your range.
Click on the Format painter (looks like a paint brush). While holding
down the left mouse button, highlight the rest of your range and
release. Or you could go to the first cell in your range (assuming it
is a column) move the mouse over the lower right hand corner of the
first cell until it changes to a black cross and double click. That
will copy it down the range.

Steve