Excel 2007 : Need help to be able to change colors of a cell based upon the date in the cell...

Hi all,

I am new here, though not new to MS Excel. I am in the military, and have a good basic knowledge on how to use Excel 2007, and up to this point I've been fine with that. However, I've been tasked with coming up with a Certification Tracker that will change colors as the expiration date for that Soldiers certification comes within 30 days of expiring (AMBER), and then again when it has expired (RED). All dates are good for exactly one year, and should change to AMBER after 335 days have passed from the date entered onto the cell, and then change to RED after 365 have elapsed. Also, any cells that do not contain a date should automatically go to RED. The cells utilized for dates are block selected from C2 to O52. These features need only be applied to the "Annual Certifications" sheet, and not affect the "Additional Training" sheet in the same Workbook.

Hopefully my description of what I am trying to accomplish is clear. I've tried to figure out how to accomplish this within Excel itself, but it seems I need to know coding to pull this off. Anyone willing to help by posting the code necessary to help accomplish this task would be greatly appreciated. If requested, I can provide the Certification Tracker as it currently exists, without sensitive information.

Thank you all in advance for your help.

Hi dcorsivo and welcome to the forum.

Giving us a look at the layout of your data is always prefered. It sounds like you need conditional formatting of your data based on todays date.

Read
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
http://chandoo.org/wp/2010/01/05/con...matting-dates/
http://spreadsheets.about.com/od/adv...atting-hub.htm
http://www.mrexcel.com/articles/due-...formatting.php

to see what I'm thinking you need to learn.

Hi,

Look at the attached and the two conditional formatting rules I've created. I think this is what you want.

Change some of the dates and see if they meet your requirements.

Look at rules for conditional formatting. Manage the rules and add a rule to make greater than 365 red.

See if the attached is what you want now. Change some dates and see if the format correctly. Look at the 3 CF rules I have and see if you understand them. If not read those links above or learn more about Conditional Formatting.