formula to change font or cell colour

[Dave]

I'm a bit of a novice at excel and tend to learn better from examples than
reading through instruction. I know you can use condtion format to change
font or cell colour but I don't know how to work out the formula. I have a
simple membership spreadsheet with cell b4 as the start date, cell d4 as the
expiry date which is b4+365. What I want to do is try and get the expiry date
cells to go red 30 days before the due date but still be able to see the due
date. The start date cells are the only cells that will change everytime
membership is renewed, the expiry cells will always be start date+365. I hope
you understand what I am trying to get at. What I am after is a method of
flagging up memberships that are due to expire 30 days before so I can send
out reminders. Please, Please Help, I'm using Office 2003 at home but Office
2000 at work, I normally create my spreadsheets at home and then use them at
work.

 

[Peo Sjoblom]

Select D4, do format>conditional formatting>formula is and use

=D4-30<=TODAY()


click format button and change the fonts to red or the pattern to red

make sure the computer date is correct



--


Regards,


Peo Sjoblom

 

[Dave]

I don't know if I'm doing something wrong, but this did nothing. All I got
was a blank d4, or if I put in the expiry date and then use the conditional
format and formula below I don't get any changes, if I put the formula B4+365
in D4 then activate the cell and conditional format I get nothing. Eample of
what I'm after:
Member Start Paid Expire
King Kong 10 Dec 06 £30.00 10 Dec 07

As you can see the 10 Dec is within the 30 days from todays date
I need this to change colour 30 days before the expire date to remind me to
send out an rewnewal form.

 

[Pete_UK]

Follow Peo's instructions, but use this formula next to the Formula Is
dialogue box:

=D4>=TODAY()-30

You can use the Format Painter to copy the format down to other cells
in the column.

Hope this helps.