VBA lookup and return text in different column

Question

I'm working on a list of tasks where i have a status column (H) and a deadline column (J) around 1000 rows. Starting from row 7

If a task gets the state "Closed" the date in the same row but in the deadline column should be over written with the text "Done"

Of cause I could do "If" and "Then" commands for each row but the code will be soooo long. Thinking of the Lookup function that could be a fast and simple way of doing this but I'm not that familiar with the Lookup function. Any help would be appreciated.

I have attached a picture for better understanding.

Thanks in advance /Martin

Answer 1

Step 1: Run InitialUpdate once first to update all column J data.

Sub InitialUpdate()
    Dim rngData  As Range, arrData
    Dim lastRow As Long, i As Long
    lastRow = ActiveSheet.Cells(Me.Rows.Count, "H").End(xlUp).Row
    Set rngData = ActiveSheet.Cells(7, "H").Resize(lastRow - 7 + 1, 3)
    arrData = rngData.Value
    For i = 1 To UBound(arrData)
        If UCase(arrData(i, 1)) = "CLOSED" Then
            arrData(i, 3) = "Done"
        End If
    Next
    ' Disable change event
    Application.EnableEvents = False
    rngData.Value = arrData
    Application.EnableEvents = True
End Sub

Step 2: Implement Worksheet_Change which handles ongoing daily inputs.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, rngData  As Range, arrData
    Dim cell As Range
    Set c = Application.Intersect(Target, Me.Columns("H"))
    If Not c Is Nothing Then
        Application.EnableEvents = False
        For Each cell In c
           If UCase(cell.Value) = "CLOSED" Then
                cell.Offset(0, 2) = "Done"
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

Note: Worksheet_Change can validate the whole column H each input. I prefer to use InitialUpdate optimizes bulk update.