I have an excel sheet with a test date and expiration date.How can I change the color as the expiration nears?

Question

This spreadsheet tracks mandatory training and expiration dates of required training. I have a test taken date, and an expiration date. I would like to change the color of the expiration date as the date gets closer to expiration. 1-180 days green, 181-91 days orange, 90 days to 1 day red. How can I do this?

Answer 1

Conditional formatting will work but not just by itself... You need a way to automatically update the formatting every diay the spreadsheet is opened.

You can do this with a macro and the worksheets activate event

Private Sub Worksheet_Activate()
Me.Cells(1, 1) = Now()
End Sub

this bit of code will update cell A1 with the current system date/time. You can then use this cell in formulas to compute days since exam. Then using those results perform conditional formatting to change the color.

If you use column A to hold the NOW() in row 1 and subsequent computed days since test in the following rows. You can hide that column fand the first row from view.

If you want to go further the entire formatting thing can be done entirely with VBA so you would not have to hide rows or columns.

Answer 2

If the cell with the current date is A1, and this could be accomplisht by usint this formula in A1:

=Today()

then set conditional formatting to be these conditions:
Condition 1
Cell value is geater than =$A$1+180
Format green

Condition 2
Cell Value is less than =$A$1+90
Format red

Condition 3
Cell Value is between =$A$1+180 and $A$1+90
Format orange

Answer 3

Use Conditional Formatting on the Format menu.