Change the tab colour based on multiple date enties

[cwcwcw]

Hi, I have a worksheet that has 20 different expiry dates, they are individual cells, not in a range. If any one of these cells become expired I would like the tab to be highlighted, I am a complete beginner at this so sorry if I've got any definitions wrong and thanks.

 

[John_w]

See if this Worksheet_Calculate event handler works for you. The code must be put in the sheet module, so to insert the code, right-click the sheet's tab and click View Code, and paste the code.

Private Sub Worksheet_Calculate()

    Dim cell As Variant, expired As Boolean
    
    expired = False
    For Each cell In Split("A5,A10,A15,A20,A25,A30", ",")
        If Range(cell).Value <= Date Then expired = True
    Next

    If expired Then
        Me.Tab.Color = vbRed
    Else
        Me.Tab.Color = vbWhite
    End If
    
End Sub

Change the cell addresses shown to suit. The sheet tab changes to red when any of the dates in those cells is less than or equal to today's date.

 

[cwcwcw]

Thanks John, I think I can see how that works, I'm still working on my spread sheet so not quite ready to input this yet, I'll be sure to let you know how it goes tho. thank you, chris

 

[cwcwcw]

Thanks again john, works perfectly.
Chris