I would like row to highlight when the date within it, is withing 14days of todays date or passed.

Question

I have a row that contains the expiration date of qualifications. I would like the cells to be highlighted when the date within the cell is within 14 days of todays date.

Is this possible?

Many thanks

Answer 1

Hi @Rob Willingham, 

Thank you for posting your question in the Microsoft Q&A forum. Based on your description, I understand that you have a row containing expiration dates for qualifications, and you’d like the cells to be highlighted when a date in the row is within 14 days from today (meaning the qualification will expire in the next 14 days). Is that correct?  If that’s correct, you can try two approaches: 

1. Using Conditional Formatting 

• Select the row (or range) that contains the expiration dates. 

• Go to Home > Conditional Formatting > New Rule. 

• Choose Use a formula to determine which cells to format and enter this formula 

=AND(A$1<>"", A$1<=TODAY()+14, A$1>=TODAY()) 

• Click Format, choose a fill color (for example, yellow or light red), then click OK, and click OK again to apply the rule. 

The result will look like this: 

2. Using VBA for Automation 

If you prefer a macro-based solution:  

• Press Alt + F11 to open the VBA editor. 
• Insert a new module and paste the code (Adjust the range (A1:F1) to match your row) 

Sub HighlightExpirationDates() 
    Dim ws As Worksheet 
    Dim dateRange As Range 
    Dim cell As Range 
    Dim daysUntilExpiry As Long 
     
    Set ws = ActiveSheet 
    Set dateRange = ws.Range("A1:F1")  
     
    For Each cell In dateRange 
        If IsDate(cell.Value) Then 
            daysUntilExpiry = cell.Value - Date 
            If daysUntilExpiry >= 0 And daysUntilExpiry <= 14 Then 
                cell.Interior.Color = RGB(255, 255, 0)  
            Else 
                cell.Interior.Color = xlNone  
            End If 
        End If 
    Next cell 
End Sub 

• Close all dialog boxes to return to the worksheet. Then press Alt + F8, select the macro from the list, and click Run.

The result will also look like this: 

---

I hope these two approaches help you achieve what you’re looking for! If I misunderstood your requirement or if you have any further questions, please feel free to reply under this post. I’ll be happy to assist further and make any adjustments as needed. 

Thank for your time! 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.