Show message if specific text in cell & date check are met

[ipbr21054]

Morning.
On my worksheet in column G will be various dates & Text.
In column A will be a date.

The code advice im looking for is as follows.
If text in column G = POSTED & date in column A is 30 days old then show Msgbox
Once im shown the Msgbox i can take it from there so it doesnt have to check for more than 30 days.


Thats it.
Thanks

 

[ipbr21054]

Hmmm Another good question.
Honest answer then not sure.

If this is going to be an issue then i will advise users to just manually scroll up & look,not ideal but dont want helpes to get an headache over it when trying to help me.

Thanks

 

[NoSparks]

If this is going to be an issue then i will advise users to just manually scroll up & look

When the user scrolls up what would they be looking for ?

 

[ipbr21054]

The value POSTED in column G 30 days from the current date.

The whole point of this is when i can open a claim with the Post Office.
Before 30 days is too early.
So you can only start a claim at 30 days.

The daily workload within 30 days could be real busy or just mild so the amount of rows the user needs to scroll up checking will vary.

I just thought a simple code which when the sheet is opened etc would make the task quicker & much better time saving exercise.

 

[NoSparks]

I don't care why they are looking for something, I only want to know what they are looking for.

When The value POSTED in column G 30 days from the current date doesn't exist
what would they then be looking for ?

 

[NoSparks]

Does this work for you ?

Private Sub Worksheet_Activate()
    Application.Goto Sheets("POSTAGE").Range("A" & Rows.Count).End(xlUp), True
    ActiveWindow.SmallScroll UP:=14
    
    Dim cRow As Long, lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row

    ' work from bottom up
    For cRow = lastRow To 7 Step -1
        ' check for POSTED
        If Range("G" & cRow).Value = "POSTED" Then
            ' check if more than 29 days back
            If Range("A" & cRow).Value < Date - 29 Then
                ' display message when more than 29 days back
                MsgBox "MOST RECENT DATE MORE THAN 29 DAYS OLD" & vbLf & vbLf & _
                       Range("A" & cRow).Value & vbLf & vbLf & _
                       "WHICH IS " & Date - Range("A" & cRow).Value & " DAYS OLD" & vbLf & _
                       "AND ON ROW " & cRow, vbCritical, "START CLAIM FOR NO SIG MESSAGE"
                ' select that date
                Range("A" & cRow).Select
                ' don't look any further
                Exit For
            End If
        End If
    Next
    
End Sub