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

 

[myall_blues]

Air code ...

I assume you're doing this on a row by row basis?

...
Dim cRow as long, lastRow as long
...
For cRow=1 to lastRow

 

[ipbr21054]

Sorry i dont understand that.
Can you show me the code in full please

 

[myall_blues]

Yeah sorry it posted too quickly when I pressed enter for some reason.

Air code ...

I assume you're doing this on a row by row basis?

...
Dim cRow as long, lastRow as long
...
For cRow=1 to lastRow
    If Range("G" & cRow).Value = "POSTED" And Range("A" & cRow).Value = Date - 30 Then
        Msgbox "My message"
    Endif
Next
...

 

[ipbr21054]

The goal here is to keep a check on items that have not yet been delivered on a 30 day period from todays date.
So these in question would still have POSTED in the column G

The code should check 30 days only as items that have been posted say 25 days ago will be flagged up correct & that is then to early from me to act on.
Example.
21/10/2024 POSTED This would flag up & show Msgbox
22/10/2024 POSTED but No Msgbox until 22/11/2024
24/10/2024 POSTED but No Msgbox until 24/11/2024
29/10/2024 POSTED but No Msgbox until 29/11/2024
Makes sense ?

I put the code as shown but i dont see any Msgbox.

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

For cRow = 1 To lastRow
    If Range("G" & cRow).Value = "POSTED" And Range("A" & cRow).Value = Date - 30 Then
        MsgBox "My message"
    End If
Next
End Sub

 

[NoSparks]

You aren't calculating what the lastRow is
and the message will only show if the Date is exactly 30 days

 

[ipbr21054]

Ok ,so i await reply from user that posted to advise how i continue with regards last row.

You mention exact 30 days,what im trying to do is not be shown day 20, 27, 29 etc as then its to early to act.
So when i open the worksheet the code should hopefully show me if a POSTED value is found on say day 30 to advise me to go then start a claim.

I am also open to other suggestions if you have something to try.
Thanks

 

[NoSparks]

Based on a file you've previously linked to
your sheet has over 2200 rows
and column A is text not real dates

You would be best to approach this question in a manner similar to the solution for your question here

 

[ipbr21054]

The rows that there are many wouldn’t really come into play.
From the current days date back 30 days wouldn’t be that many rows.
I understand your post though thanks.

 

[NoSparks]

Why don't you just try the Range.Find method ?
Think you'll discover the number of rows is really of no concern.