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]

No when i put that code in address bar for cell D2303 i see FALSE see screenshot

Column A are formatted as date

 

[myall_blues]

It doesn’t matter that the column or cell is apparently formatted as date. Do you see the ^ character before the date - that means it is being treated as text.
Please check under Settings > Excel Options > Advanced > Lotus Compatibility and if necessary uncheck the box for Transition navigation keys
Even after doing that you may still need to convert the text to proper dates.

 

[ipbr21054]

Ah ok thats new for me so i have removed what was advised.
I have done the ISNUMBER again just to show you.

Please advise what my next step is Thanks.

 

[NoSparks]

If you would step through the code with the F8 key you would see if it is the POSTED part
or the date part of the procedure that is false and therefore not showing the message.

The first thing you said in post 12 was
I have changed / formatted column A from Text to Date
but I don't think so.

Try this, it will check rows from the bottom up (to a max of 50 rows) for the first text that looks like a date
that is more than 29 days back from today. If there is a text that looks like a date that is 30 days back
that is the one that the message will show for.

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 lastRow - 50 Step -1
        ' check for  POSTED
        If Range("G" & cRow).Value = "POSTED" Then
            ' check if more than 29 days back
            If DateValue(Range("A" & cRow).Value) < Date - 29 Then
                ' select, that date
                Range("A" & cRow).Select
                ' 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 - DateValue(Range("A" & cRow).Value) & " DAYS OLD" & vbLf & _
                       "AND ON ROW " & cRow, vbCritical, "START CLAIM FOR NO SIG MESSAGE"
                ' don't look any further
                Exit For
            End If
        End If
    Next
    
End Sub

 

[ipbr21054]

I will try once home.

I clicked in each cell in column A where looking at the top I could see TEXT written.
I selected that column & right clicked format & selected DATE.

Now when I click in any of those cells & look at the top DATE is what I see.
This is why I say I e changed it from TEXT to DATE.
I assume this is the correct way for what you are talking about ?

 

[NoSparks]

I will try once home.

?

 

[ipbr21054]

Sorry i thought i replied.

Yes that works fine.

Many Thanks for the code.