I need cell to automatically change color based on date in different cell

I am setting up a deadline sheet and I would love it if I could get the projects to change color based on how close they are to deadline. I really only require green, yellow and red. So for example, if A2 is 9/20/2013 and the date is 9/1/2013, I would like B2, C2, D2 and E2 to be green. when today's date is 9/10, I would like them all to change to yellow and when the today's date is 9/20 or beyond I would like the cells to change to red.

is this too complicated or can it be done? I am a complete novice, so thank you all in advance!

You can use conditional formatting for this and select "use a formula to determine which cells to format": =B2>$A$2 (format fill color as red), =B2=$A$2 (format fill color as yellow), =B2<$A$2 (format fill color as green)

Make sure the range is B2:E2. You can ensure the correct range by highlighting all cells that need to be colored before clicking on conditional formatting. Or you can change it in the conditional formatting menu.

That looks like what I need except for one detail, I would like it to turn yellow 10 days before the deadline. then on or after the deadline turn red. I may be wrong, but the way I see this formula is that it will turn yellow ON the deadline then red after and any day prior to deadline is green? Thanks for the quick help BTW!

All in the same formula or in different conditions? Sorry, I did announce that I am a newb, lol

No worries. We all start at the same place, just not at the same time. Each of the formulas need to be in a "New Rule". And just use the formula part, starting with the = sign. You'll see a button that says "Format..." and then select the "Fill" tab and pick your colors (green, yellow, or red).

This is using Excel 2010. I'm not sure how other versions may be different, but the gist is the same.

Jeff, Thank you! I appreciate the help!

What I need is a combination of the two answers I received. This is how it is set up
Under A starting with row 2 I have a series of dates. 09/16/2013 - 09/20/2013 in columns b - f I have 5 people. under each name I will put in a project tobe due on a date in column A. I want the cells with projects in them to change from green to red depending on how close they are to the date in colum A. I don't want to have to enter the date in each field I want formatted. I just want to be able to open the spreadsheet and see that the project is close to the due date or overdue. it's not a huge deal right now, but as I add more writers to my staff, I can imagine this would be a valuable tool.

Also, once I have the color thing straight, is there a way to add a check box to say that the project is complete and keep the current color so I can see patterns? Like if people constantly cut it close to deadline, then I could see that by looking at the sheet?

So as we get closer to 9/16 Work would go from green to yellow. Then when 9/16 is past, it would turn red.

The last peice of the puzzle is this... once the assignment is handed in, I would like an indicator. like a check box, just so I know its in. this would also turn off formatting, like (if box is checked, no formatting)

But I am just focused on the formatting by date thing for now. I hope this helps, I can't upload images from work pc.



Tommy F. | Mike B. | Rob G. | John D.
9/16/2013 | work | | |

9/17/2013 | | Project | |

9/18/2013 |

9/19/2013 |

9/20/2013 |

that looks horrible.

do I need to wait until I get home?
lol. If you have an e-mail address I can forward a copy of the sheet I am working on

I would refrain from using check boxes because I believe you'll have to get into VBA coding to make it work how you want it to. My advice is instead of check boxes, make a new column for a "completion date". This way you can still use conditional formatting. I've created a dummy spreadsheet to show you. Example1.xlsx Note: I've only done the conditional formatting for the first person.

*In my rush, I forgot to clean up the spreadsheet. Please ignore rows 10-14. Rows 16-20 hold the formulas that the conditional formatting uses.

A question just popped into my head... what if there is no project due that day? Did you want it to remain blank, or will there always be something due?

Exactly, if there is no project it would be light blue

See attached again. I know you previously said you didn't want to enter more dates, but adding a completion date is the only way that I know of without using vba.

http://www.winningmark.com/2012/01/1...ple-deadlines/

this is a link to something I found that is real close to what I'm looking for, only it is way more complicated than I need it to be. I only need the first section really. I like the way it is set up. If you could distill that down to what they have in columns a-d and add the "completed (yes/no)" collum, that would be fantastic.

Again, thank you guys for all the help, excel is not my thing... I'm a story teller.

Okay... take a look at this:

First off, for this to work, you have to have Iterations box checked. If Excel 2010 - click File, Options, Formulas, Enable iterative calculation (checked), and set Maximum Iterations: to 1. If you have an earlier version of Excel, I think it's under Tools -> Options -> Calculations.

Columns G, H, and I can be deleted - these hold the same formulas as the conditional formatting and it was just for me to visualize how the formulas work.

Column F (TimeStamp) cannot be deleted. However you can hide this column by right-clicking on the F and selecting "Hide". This way you'll never see the dates. Essentially, this puts the current date and time as soon as anything gets entered in Column E (and I mean anything, you could write "no", and it would still count). If you delete a response "y" or otherwise in column E, the corresponding time-stamp will also be reset.

The reason a second date field is necessary, such as a time stamp, is because without it, the conditional formatting cells would all eventually turn to red because due dates will eventually be in the past, and so you'll never know if people turn projects in early, or wait til the deadline. It's that second part of the process, keeping the color the same based on when the project is completed - that's where we need to compare the due date to the completion date (or in this case, the TimeStamp). There may be a way to do it with macros or vba code, I just don't know how, but here's a resource I was looking at. http://www.mcgimpsey.com/excel/timestamp.html

This is currently set up for 1000 rows. After row 1000, you'll have to click and drag cells A1000 and F1000 down.