Upcoming expiry date warnings that turn cell contents red

Hi
I have a spreadsheet with people's certificates (eg. CPR) with the date the
cerficiate expires. I want to be able to open the spreadsheet at any time
and have anyone whose certificate is one month from expiring, be red (I want
the whole cell to turn red.) I've tried conditional formatting but can't
seem to get it. Any help would be greately appreciated!!

Thanks

Heather

--
Heather

Good evening Heather

What I think you need to do is set up a cell somewhere with todays date (=NOW() will always update to todays date when you open the spreadsheet). In my example, cell E4 contains todays date, D4 the expiry date. Go to Format > Conditional Formatting and select Formula Is and enter this formula :
=(E4-D4)<30
and then set up the cell / font colour etc and click OK.

HTH

DominicB

Heather

assuming a header row and that the dates are in column A, then something
like:

Conditional Formatting Formula is: =TODAY()>$A2+30

Copied to all the cells you want to turn red

Regards

Trevor


"Heather via OfficeKB.com" <u14448@uwe> wrote in message
news:552746cbb541d@uwe...
> Hi
> I have a spreadsheet with people's certificates (eg. CPR) with the date
> the
> cerficiate expires. I want to be able to open the spreadsheet at any time
> and have anyone whose certificate is one month from expiring, be red (I
> want
> the whole cell to turn red.) I've tried conditional formatting but can't
> seem to get it. Any help would be greately appreciated!!
>
> Thanks
>
> Heather
>
> --
> Heather

Thanks for the response.
Seems to be working! Thank you so much!! Now I can stop pulling my hair out:
)

Heather

dominicb wrote:
>Good evening Heather
>
>What I think you need to do is set up a cell somewhere with todays date
>(=NOW() will always update to todays date when you open the
>spreadsheet). In my example, cell E4 contains todays date, D4 the
>expiry date. Go to Format > Conditional Formatting and select Formula
>Is and enter this formula :
>=(E4-D4)<30
>and then set up the cell / font colour etc and click OK.
>
>HTH
>
>DominicB
>

--
Heather


Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200510/1