#1   Report Post  
Kevin
 
Posts: n/a
Default 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

  #2   Report Post  
David McRitchie
 
Posts: n/a
Default

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



  #3   Report Post  
Kevin
 
Posts: n/a
Default

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




  #4   Report Post  
David McRitchie
 
Posts: n/a
Default

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






  #5   Report Post  
David McRitchie
 
Posts: n/a
Default

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.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional format problem PJ Excel Discussion (Misc queries) 2 March 11th 05 03:05 PM
copy conditional format as ACTUAL format Dana Zulager Excel Discussion (Misc queries) 7 December 8th 04 12:02 AM
Conditional format rexmann Excel Discussion (Misc queries) 4 December 2nd 04 01:01 PM
Copying a conditional format Meaux Excel Worksheet Functions 2 November 29th 04 11:19 AM
Conditional Format With SUMIF Minitman Excel Worksheet Functions 3 November 1st 04 03:58 PM


All times are GMT +1. The time now is 02:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"