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

 

[ipbr21054]

Sorry to say but for some reason that also didnt work for me.
I even put the value POSTED in many cells then tried it again but never did i see the Msgbox appear

 

[ipbr21054]

Thanks for the effrorts but i think we will put this to bed & have users check it manually

 

[myall_blues]

One more thing to try - can you put the formula =ISNUMBER(A2302) in a cell somewhere (assuming A2302 has a date in it) and tell us if it returns TRUE or FALSE.

 

[ipbr21054]

Not quite sure whats happening.

Row 2302 has a date in column A
Column D is empty so in the address bar i enter =ISNUMBER(A2302) & then enter.
I just see the value =ISNUMBER(A2302) now in that cell


I put the code in a row after 2302 & now i see FALSE

 

[myall_blues]

Column D is formatted as text. When you put it in the row after, does it say A2302 or A2303?

Is it points to A2302 then there is your problem - the values in column A are text not dates. We can fix it if you tell us what your date format looks like.