Expiry date

[Martina]

Dear experts,
I have columns with expiry dates for first aid certificates. How do I
change the colour of the cell to alert me to the fact that the date for
renewal has arrived.
kind regards
Martina

 

[Martina]

Hi experts again,
I did find the TODAY function which I can use in the conditional formatting
area. Works fine. It does raise another question though. What if I want to
have another cell somewhere else on the sheet (say A4) which looks at the
cell with the date in it (Z4) and if Z4 has expired A4 will turn red also.

I have tried to make it happen, and it does but I get the date displayed in
A4 aswell. I only want the cell to turn red, I don't want any text in it.
I want it to be like an alert button to say that a date has expired in
another part of the sheet. Is this possible?

regards
Martina

 

[macropod]

Hi Martina,

You can do this with conditional formatting:
.. Select any cell with an expiry date
.. Choose Format|Conditional Formatting, then
. select 'less than' from the second drop-down box
. enter '=TODAY()' in the third box
. click Format|Patterns and choose the colour you want
. click OK, OK
.. Copy the cell
.. Select the other cells with expiry dates
.. Choose Edit|Paste Special>Formats>OK

Cheers

--
macropod
[MVP - Microsoft Word]


| Dear experts,
| I have columns with expiry dates for first aid certificates. How do I
| change the colour of the cell to alert me to the fact that the date for
| renewal has arrived.
| kind regards
| Martina

 

[Martina]

Hi Macropod,

Thank you the TODAY function works fine. It does raise another question
though. What if I want to have another cell somewhere else on the sheet (say
A4) which looks at the cell with the expiry date in it (Z4) and if Z4 has
expired A4 will turn red also.

I have tried to make it happen, and it does but I get the date displayed in
A4 aswell. I only want the cell to turn red, I don't want any text in it.
I want it to be like an alert button to say that a date has expired in
another part of the sheet. Is this possible?

Can a cell in the sheet adopt the formatting settings of another cell in the
sheet without taking on the text/numeric entry?

regards
Martina

 

[David McRitchie]

You can use Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

 

[macropod]

Hi Martina,

You can do this too with conditional formatting:
.. Select any cell that you want to change (even one with an expiry date)
.. Choose Format|Conditional Formatting, then
. select 'formula is' from the first drop-down box
. enter '=A1=TODAY()' in the second box
. click Format|Patterns and choose the colour you want
. click OK, OK
.. Copy the cell
.. Select the other cells with expiry dates
.. Choose Edit|Paste Special>Formats>OK
In the above example, 'A1' is the address of the cell with the expiry date. You can also use absolute row/column referencing if you
want (eg if you want to apply the formatting to a number of columns on the same row, based on the value in A1, you might use '$A1').

BTW, with both examples I've posted, if you change '=TODAY()' to '<=TODAY()', cells with expiry dates on or before today will active
the conditional formatting.

Cheers


--
macropod
[MVP - Microsoft Word]


|
| > Hi Macropod,
| Thank you the TODAY function works fine. It does raise another question
| though. What if I want to have another cell somewhere else on the sheet (say
| A4) which looks at the cell with the expiry date in it (Z4) and if Z4 has
| expired A4 will turn red also.
|
| I have tried to make it happen, and it does but I get the date displayed in
| A4 aswell. I only want the cell to turn red, I don't want any text in it.
| I want it to be like an alert button to say that a date has expired in
| another part of the sheet. Is this possible?
|
| Can a cell in the sheet adopt the formatting settings of another cell in the
| sheet without taking on the text/numeric entry?
|
| regards
| Martina

 

[Martina]

Thank you very much. It's working All looks very pretty.
regards
Martina