Backgroung color changing by date range

  • Thread starter Thread starter DaveSlinger
  • Start date Start date
D

DaveSlinger

I have a worksheet with expiration dates in the cells. Every month som
of those dates in the columns come due.

At the present time I have to manually search each column in th
worksheet and change the background cell color when the expiration dat
is approaching (i.e. yellow for 3 days left, red for expired).

Is there some way that Excel can highlight the cell background color
automatically?

Is there an add-in or a vendor that can make this possible?

I'm not a "techie" and some of the replies to other member question
are difficult for me to understand. Please explain in "dummie" terms.

Thank
 
You need conditional formatting.

Select all of the dates.

Goto menu Format>Conditional Formatting
Change Condition 1 to Formula Is
Add a formula of =A1<TODAY()
Click Format
On the Patterns tab, select Red
OK
Add another condition
Change Condition 2 to Formula Is
Add a formula of =A1<TODAY()+3
Click Format
On the Patterns tab, select Yellow
OK
OK

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks, I'm trying your suggestion now.

If there is a date in the cell (12-25-03) what is the formula to change
the background color based on the date in the cell?
 
Dave,

This is assuming that the cell contains a date. That is why it uses TODAY()
to get a number of days.

Or are you asking a slightly;by different question?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I can't seem to get the formula in a single cell to do a "count down".
But I can tell that you have me on the right track.

In November when I entered an expiration date, I change the cell t
green to show me that it's okay and I have a copy of it on file. Th
license expiration date I entered was 12-21-03.

If today was 12-18-03 the cell would automatically turn yellow (whic
is three days before the expiration date). I can then notify the part
that this license is about to expire. Hopefully I would get a new cop
of this license and put in the new future expiration date which woul
keep the cell green.

However if today was 12-21-03 and I still had not received the ne
license the cell would automatically turn red. In this case I woul
tell not be able to use this party because this license is expired.

I can see from what you have shown me that I can change the cell colo
fairly easy. I can't seem to find out the rest of the formula though.
I have tried the < and the > signs. I looked through my Excel Dummie
book for conditional formating info but I guess it doesn't go into tha
much depth.

I tried adding the three conditions but I'm missing something I think.
As a test, when I type in a different date in the cell which I thin
should trigger a change in color, nothing happens.

Thanks for your help
 
Dave,
When you entered the formulas in the conditional format did you adjust the A1 cell reference for your date range? This cell reference needs to be the reference for the top left corner cell of the range you have selected.

Bob's solution should give you just what you need.

Good Luck,
Mark Graesser
(e-mail address removed)

----- DaveSlinger wrote: -----

I can't seem to get the formula in a single cell to do a "count down".
But I can tell that you have me on the right track.

In November when I entered an expiration date, I change the cell to
green to show me that it's okay and I have a copy of it on file. The
license expiration date I entered was 12-21-03.

If today was 12-18-03 the cell would automatically turn yellow (which
is three days before the expiration date). I can then notify the party
that this license is about to expire. Hopefully I would get a new copy
of this license and put in the new future expiration date which would
keep the cell green.

However if today was 12-21-03 and I still had not received the new
license the cell would automatically turn red. In this case I would
tell not be able to use this party because this license is expired.

I can see from what you have shown me that I can change the cell color
fairly easy. I can't seem to find out the rest of the formula though.
I have tried the < and the > signs. I looked through my Excel Dummies
book for conditional formating info but I guess it doesn't go into that
much depth.

I tried adding the three conditions but I'm missing something I think.
As a test, when I type in a different date in the cell which I think
should trigger a change in color, nothing happens.

Thanks for your help.
 
Dave,
Did you get it working?

Regards,
Mark Graesser
(e-mail address removed)

----- DaveSlinger wrote: -----

Okay, thanks for your help guys.
DAVE
 
Back
Top