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]

As @NoSparks has already advised:

• you haven't set the value of lastRow. See updated code below
• If the first row that you want to check is not row 1 then you need to change the statement For cRow = 1 To lastRow
  to For cRow = x To lastRow where x = the number of the first row.
• If your dates are text then none of this will work and you will need a different solution.

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

    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

 

[ipbr21054]

I have changed / formatted column A from Text to Date.
I am unsure what the first row to check will be.
I wanted the code to look at column A for a date 30 back from todays date then check for the POSTED value.

My current row is at 2302 so from todays date & back 30 could be 22 rows or maybe 48 row,i dont know this answer.
This is why i was asking the code to find the row that is 30 days back & see if POSTED value was present in column G
If Yes show Msgbox box but if No then do nothing.
This would be checked each time the sheet each opened but more to the point each morning iot would check then advise a Msgbox so the user can open a claim should POSTED value be on a 30 day check.

Claims can only be opened on 30 day & not before.
As values are beeing added to the sheet & we dont know if 3 or 157 of them the sheet moves up & off the screen so the user cant see them.
Trying to get around each morning the user having to scroll up the page looking for POSTED values when i would like the code to do this.

 

[myall_blues]

I am unsure what the first row to check will be.

It doesn’t matter that you don’t know. It should be the first row of data. So now you have 2302 rows, but what row does it start at? Row 2, row 5, row 100?
The code works by scanning down from the first row until it finds a date that is 30 days before the current date.

So for example if the ‘first’ row of data is row 5, and 30 days ago occurs at row 2275, it will scan downwards from row 5 until it gets to row 2275, and then it will display the msgbox, as long as there is POSTED in column G also.

 

[ipbr21054]

My database is in reverse if that makes sense.
Row 7 is 02/01/2017

Current Row is 2302 21/11/2024

So look at current date of last row then go up the page 30 days

Make Sense ?

 

[myall_blues]

So look at current date of last row then go up the page 30 days

Make Sense ?

Yes it does. Do you want to see if it is 30 days before today’s date, or 30 days before the last entry?

 

[ipbr21054]

Good question.
I think Todays date please

 

[myall_blues]

Then the code works as it is. It works going forwards the same as going backwards. You just need to change:

For cRow = 1 To lastRow

to

For cRow = 7 To lastRow

 

[ipbr21054]

Like this correct as i see no Msgbox pop up.
As a test i wrote POSTED in cells where the dates started from 18/10/2024 up to 24/10/2024 so it would catch one of them.
I would then one by one replace POSTED with the correct value then select another sheet them coming back to the sheet untill i see which date it actually triggers on.

I didnt see any Msgbox for any of them

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

    For cRow = 7 To lastRow
        If Range("G" & cRow).Value = "POSTED" And Range("A" & cRow).Value = Date - 30 Then
            MsgBox "START CLAIM FOR NO SIG", vbCritical, "START CLAIM FOR NO SIG MESSAGE"
        End If
    Next
    
    
End Sub

 

[ipbr21054]

Your code line below is checking from the start at Row 7 right through to the currect row which is 2302
This seems wrong to me as over time the rows will get longer & were checking from Row 7 all the time.

This is why i asked from current row then backwards so the number of rows to check will be far less than what its currently doing

For cRow = 7 To lastRow

 

[NoSparks]

In the file you previously linked to column A dates for October were 1, 7, 12, 14, 15, 16, 20, 21 and 22
so what is to happen if the date 30 days back from Today does not exist in column A ?