Conditional format
 

I am making a training schedule, and I need a bit of help.
In each workbook, I have 12 sheets 1 for each month.
Lets say Joe is up for retraining on March 1. In cell C46 of every month I
have his due date of March 31. The way I have it working right now is if 30
days prior and the training is still not done, the cell with the training
date in it (C46) turns yellow. If it expires it goes red. What I would like
it to do, is if 30 days prior the training is not done I would like it to
turn Cells A1-A30 B1-B30 C1-C30 yellow. If it expires I would like the same
cells to turn red. Is this possible.

Thanks
Kevin

Hi Kevin,
If you mean what you say literally then
you would select cells A1:C30 before entering your Conditional Formatting
formula -- those are the cells that will get colored. If you would like
C46 to also change color then select A1:C0 and C46

Since you want the entire thing to be based on a single cell your
formula would be
=$C$46 date(2005,03,31) -- format as Red
=$C$46 date(2005,03,1) -- format as Yellow

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kevin" wrote in message ...
I am making a training schedule, and I need a bit of help.
In each workbook, I have 12 sheets 1 for each month.
Let's say Joe is up for retraining on March 1. In cell C46 of every month I
have his due date of March 31. The way I have it working right now is if 30
days prior and the training is still not done, the cell with the training
date in it (C46) turns yellow. If it expires it goes red. What I would like
it to do, is if 30 days prior the training is not done I would like it to
turn Cells A1-A30 B1-B30 C1-C30 yellow. If it expires I would like the same
cells to turn red. Is this possible.

Thanks
Kevin

Hi David,
Thanks that did work but what if I have a few different cells with different
dates in them for different trianing?

eg: c46 march 1 d46 june 1 e46 april 1

So I would like A1:C30 to turn red when any of the cells gets to the date
and yellow 1 month previous.

Thanks

Kevin


"David McRitchie" wrote:

Hi Kevin,
If you mean what you say literally then
you would select cells A1:C30 before entering your Conditional Formatting
formula -- those are the cells that will get colored. If you would like
C46 to also change color then select A1:C0 and C46

Since you want the entire thing to be based on a single cell your
formula would be
=$C$46 date(2005,03,31) -- format as Red
=$C$46 date(2005,03,1) -- format as Yellow

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kevin" wrote in message ...
I am making a training schedule, and I need a bit of help.
In each workbook, I have 12 sheets 1 for each month.
Let's say Joe is up for retraining on March 1. In cell C46 of every month I
have his due date of March 31. The way I have it working right now is if 30
days prior and the training is still not done, the cell with the training
date in it (C46) turns yellow. If it expires it goes red. What I would like
it to do, is if 30 days prior the training is not done I would like it to
turn Cells A1-A30 B1-B30 C1-C30 yellow. If it expires I would like the same
cells to turn red. Is this possible.

Thanks
Kevin

Try using COUNTIF with absolute addresses in your VLOOKUP.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kevin" wrote in message ...
Hi David,
Thanks that did work but what if I have a few different cells with different
dates in them for different trianing?

eg: c46 march 1 d46 june 1 e46 april 1

So I would like A1:C30 to turn red when any of the cells gets to the date
and yellow 1 month previous.

Thanks

Kevin


"David McRitchie" wrote:

Hi Kevin,
If you mean what you say literally then
you would select cells A1:C30 before entering your Conditional Formatting
formula -- those are the cells that will get colored. If you would like
C46 to also change color then select A1:C0 and C46

Since you want the entire thing to be based on a single cell your
formula would be
=$C$46 date(2005,03,31) -- format as Red
=$C$46 date(2005,03,1) -- format as Yellow

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kevin" wrote in message ...
I am making a training schedule, and I need a bit of help.
In each workbook, I have 12 sheets 1 for each month.
Let's say Joe is up for retraining on March 1. In cell C46 of every month I
have his due date of March 31. The way I have it working right now is if 30
days prior and the training is still not done, the cell with the training
date in it (C46) turns yellow. If it expires it goes red. What I would like
it to do, is if 30 days prior the training is not done I would like it to
turn Cells A1-A30 B1-B30 C1-C30 yellow. If it expires I would like the same
cells to turn red. Is this possible.

Thanks
Kevin

Sorry try this formula for your Conditional Format
=COUNTIF($A$1:$C$30, "" & VALUE(DATE(2005,3,31))) red
=COUNTIF($A$1:$C$30, "" & VALUE(DATE(2005,3,0))) yellow
don't know what I was thinking of when I thought your previous
formula had anything to do with VLOOKUP.

Preselect the cells that you want to receive the color, which
I think they are A1:C30, since address is an absolute address
it really won't make any difference which cell is active because
the formula is not going to be changed when applied to different
cells.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm



"Kevin" wrote in message ...
Hi David,
Thanks that did work but what if I have a few different cells with different
dates in them for different trianing?

eg: c46 march 1 d46 june 1 e46 april 1

So I would like A1:C30 to turn red when any of the cells gets to the date
and yellow 1 month previous.