(Solved) – Change status accordingly based on cell value


Hi I am currently working on a training tracker as shown below:

enter image description here

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

    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

Leave a Reply

Your email address will not be published. Required fields are marked *