Log in

View Full Version : Macro in Excel 2003


phoenix1664
Feb 10, 2015, 12:17 AM
Hey all right basically (hopefully basic) what I am looking to do is my boss has created a spread sheet of when all the people in the office go out of dxate for certain things some last 3 years some last more or less. He would like the box to do the normal amber and then red when over the alloted time but also to get the box to go red if there is no date entered. I know how to do the red if out of date for each box and the red of nothing entered but that would need to be put in each box so I am wondering if anyone would have a nice macro or an idea to aply it to the whole spreadsheet?

Thanks in advance

JBeaucaire
Feb 12, 2015, 03:20 AM
You would use conditional formatting for this.

If the date were in A1 and that date is good for 3 years, then this Conditional Formatting formula could used to create a rule to apply to that cell to turn it the color if your choice:

=OR(A1="", DATE(YEAR(A1)+3,MONTH(A1),DAY(A1))<TODAY())

Once created, the cell will be red if empty or if the date is 3 years old.

You can copy that cell once it is working and paste it down the column as needed and it will work for each cell applied.