Macro to change cell text based on expiry date

[aidan_cov]

I have a basic data list for sales leads and the status of each line is chosen from a drop-down list. I need to write a macro that will automatically change all cells that are "ongoing" to "no" once two months (or 61 days if that is easier) have expired after the original date against that particular line. The dates are in column A and status in column F. The table is in Sheet2 (cells A1:A6). This is quite a large workbook and is currently at row 13781 and increasing by about 15 rows per day. Thank you in advance for any help as I am a complete novice when it comes to VBA.

 

[rpaulson]

try this on a copy of your file.

Sub do_it()
Application.ScreenUpdating = False

    For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        If IsDate(Cells(r, "A")) = True And Cells(r, "A").Value <= Date - 60 And UCase(Cells(r, "F")) = "ONGOING" Then
            Cells(r, "F").Value = "no"
        End If
    Next r
Application.ScreenUpdating = True

End Sub

 

[aidan_cov]

try this on a copy of your file.

Sub do_it()
Application.ScreenUpdating = False

    For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        If IsDate(Cells(r, "A")) = True And Cells(r, "A").Value <= Date - 60 And UCase(Cells(r, "F")) = "ONGOING" Then
            Cells(r, "F").Value = "no"
        End If
    Next r
Application.ScreenUpdating = True

End Sub

Thanks you so much. Worked perfectly first time. Makes that job so much easier.