Moving a Row based on Formula Value

[Rob_010101]

Hello

I have created an absence spreadsheet by which when an employee reaches 4 weeks absence (column J), they are classed as Long Term Sick.

What I would like, is a piece of VB code which automatically moves the entire row to another sheet labelled LTS (> 4 Weeks), once the formula in column J returns 4 or more as the value.

FYI I'm currently using the following piece of code in the sheet's "view source" screen, which moves the entire row to an archive sheet if a list item in column O is returned as "Returned to Work".

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fromRow%, archiveRow%, archiveList As Worksheet
    If Target.Cells.Count > 1 Then Exit Sub
   
    If Not Application.Intersect(Target, Range("O2:O500000")) Is Nothing Then 'amend this range address to your
        Set archiveList = ThisWorkbook.Worksheets("Archive")
            If Target.Value = "Archive" Then
                fromRow = ActiveCell.Row
                archiveRow = archiveList.Cells(archiveList.Rows.Count, 1).End(3).Row + 1
                Range(Cells(fromRow, 1), Cells(fromRow, 15)).Copy archiveList.Cells(archiveRow, 1)
                Rows(fromRow).EntireRow.Delete
            End If
    End If
End Sub

Thanks
Chris

 

[Rob_010101]