Hi I am currently working on a training tracker as shown below:
I have manage to come up with a code to update the status (column I) if the record is expired or current.
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row Dim i As Date Dim Cell As Range Dim MyCount As Long Dim Workbook As Workbook For i = 4 To FinalRow Diff = DateDiff("d", Now, Cells(i, 8)) If Cells(i, 8) = "" Then Cells(i, 9) = "Input Review Due Date!" ElseIf Diff <= 0 Then Cells(i, 9) = "Expired" ElseIf Diff = 60 Then Cells(i, 9) = "2 Months Left" ElseIf Diff = 30 Then Cells(i, 9) = "1 Month Left" Else: Cells(i, 9) = "Current" End If Next i Columns("I:I").Select With Selection .HorizontalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With End Sub
Now for the problem, as I have to keep all past and present records, I would need the past records status to be changed from 'Expired' to 'Retrained' while present records remained as 'Current'
I am stucked with the following code sequence:
FinalRow = Cells(Rows.Count, 3).End(xlUp).Row For j = 4 To FinalRow If Cells(j, 3).Value > 1 Then Cells(j - 1, 9) = "Renewed" End If Next j End Sub