Pop up Alert When Workbook is Opened

[Gos-C]

Hi all,

I have a worksheet with range A4:Y1504. Column M is used for dispensed dates, column Y is used for closed date, and column V is used to entered the word "rebill" – if required.

When the workbook is opened, if column V contains the word “rebill”, and column Y is blank, and the dispensed date in column M is more than 7 days older than the current date, I want the following message to pop up “You have one or more “rebill” item(s) past due. Please review the row(s) highlighted black.”

I tried the following (but without any success):

Option Explicit
Private Sub Workbook_Open()
Dim rngD As Range, rngR As Range, rngC As Range, dispD As Date, rblD As Date, v As String
rngD = Range("M4:M1504")
rngR = Range("V4:V1504")
rngC = Range("Y4:Y1504")
If (rngC = "" And rngR = "rebill" And rngD < Now() - 7) Then
    MsgBox "You have one or more ''rebill'' item(s) past due.  Please review the row(s) highlighted black.", vbInformation, Title:="Rebill Alert"
End If
End Sub

The worksheet is set up to highlight the row in black if the above conditions are true.

Can someone please help me with the code.

Thank you,
Gos-C

 

[Gos-C]

Hi,

Can anyone help me?

Gos-C

 

[Sal Paradise]

Try something like this perhaps?

Option Explicit
Private Sub Workbook_Open()
    Dim i As Integer

    With Worksheets("Sheet1")
        For i = 4 To 1504
            If (.Cells(i, 25).Value = "" And .Cells(i, 22).Value = "rebill" And .Cells(i, 13).Value < Now() - 7) Then
                MsgBox "You have one or more ''rebill'' item(s) past due.  Please review the row(s) highlighted black.", vbInformation, Title:="Rebill Alert"
            End If
        Next i
    End With
    
End Sub

 

[Gos-C]

Works perfectly!

Thank you very much, Sal Paraside.

Gos-C