Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / June 2006

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Rangerbob86 - 15 Jun 2006 20:16 GMT
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!

Signature

Rangerbob86

SteveG - 15 Jun 2006 20:29 GMT
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

Signature

SteveG

Rangerbob86 - 16 Jun 2006 04:48 GMT
hey thanks! that worked. Now, is there also a way to get the formatting
to go away, once the date has passed?

Signature

Rangerbob86

SteveG - 16 Jun 2006 13:16 GMT
This should work.

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

Cheers,

Steve

Signature

SteveG

Rangerbob86 - 16 Jun 2006 16:05 GMT
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?

Signature

Rangerbob86

SteveG - 16 Jun 2006 20:03 GMT
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

Signature

SteveG

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.