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

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

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 SpecialFormatsOK

Cheers

--
macropod
[MVP - Microsoft Word]


"Martina" wrote in message ...
| 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

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

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

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Martina" wrote in message ...

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

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 SpecialFormatsOK
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]


"Martina" wrote in message ...
|
| 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

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


"macropod" wrote:

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 SpecialFormatsOK
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]


"Martina" wrote in message ...
|
| 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