I have a workbook containing list of contractors. Each contractor signs a contract containing the expiration date of his/her contract. The date for each contractor differs. Currently, the size of the worksheet is becoming unwieldy. I would like to create a pop-up in excel to remind me 14 days to the contract expiration so I can draft a new contract. Can someone please assist me.
- Option Explicit
- Sub ChangeColor()
- Dim myDate As Date
- Dim lRow As Long
- Dim MR As Variant
- Dim cell As Variant
- 'format the date excluding time
- myDate = FormatDateTime(Now, 2)
- lRow = Range("A" & Rows.Count).End(xlUp).Row
- Set MR = Range("A2:A" & lRow)
- For Each cell In MR
- If FormatDateTime(cell.Value, 2) = myDate Then cell.Interior.ColorIndex = 3 'EXP DATE - RED
- If FormatDateTime(cell.Value, 2) = DateAdd("d", 14, myDate) Then cell.Interior.ColorIndex = 4 '14 DAYS OUT - Green
- If FormatDateTime(cell.Value, 2) = DateAdd("d", 7, myDate) Then cell.Interior.ColorIndex = 6 '7 DAYS OUT - Yellow
- End Sub
Above macro set for Col A. That can be edited for any column desired. Macro begins checking on second row and below.
Day of Expiration : Cell changes RED
7 Days from Expiration : Cell changes YELLOW
14 Days from Expiration Cell changes GREEN
Thank you royUK.
Pleased to help