Check Expiry dates from multiple sheets with a pop up box

[Kjjb]

Hi ,
Surely there must be a thread for my query , but cannot seem to find it .Appreciate your help.
I have a worksheet with 4 sheets named "D1D","D2D","D3D","Misc" . I need to write a VBA to check all the 4 sheets for expiry date ( today()-30) in column E ( range E 7:E135) and then pop message should state Certificates have expired. The pop up box/check should be done when I open the workbook.
Appreciate your help.

Thanks in advance

 

[Legacy 143009]

Small revision:

Private Sub Workbook_Open()
  Dim wsNames() As string
  wsNames = Split("D1D,D2D,D3D,Misc ,Various", ",") 'You must add the sheet names you want into this list.
  For i = 0 To UBound(wsNames)
    With Worksheets(wsNames(i))
      For j = 7 to 135
        If IsDate(.Cells(j, 5).Value) Then
          If .Cells(j, 5).Value > Now()-30 Then
            MsgBox "Sheet " & wsNames(i) & " Date E" & j & " has expired."
          End If
        End If
      Next
    End With
  Next
End Sub

 

[Kjjb]

Small revision:

Private Sub Workbook_Open()
  Dim wsNames() As string
  wsNames = Split("D1D,D2D,D3D,Misc ,Various", ",") 'You must add the sheet names you want into this list.
  For i = 0 To UBound(wsNames)
    With Worksheets(wsNames(i))
      For j = 7 to 135
        If IsDate(.Cells(j, 5).Value) Then
          If .Cells(j, 5).Value > Now()-30 Then
            MsgBox "Sheet " & wsNames(i) & " Date E" & j & " has expired."
          End If
        End If
      Next
    End With
  Next
End Sub

EXCELLENT ..works perfectly.. THANK YOU VERY MUCH