With the help of theses forums i've been able to create what I think is a masterpiece!

But when I run my macro and it returns #REF (which is a result im actually ok with) it throws up an error in my if statement.

So I run XXXXX, this brings across any standing instructions, then i change a value in any cell, say for example I7 i say "Yes" actioned.

the Worksheet_Change sub cracks the sads

I was thinking I might have to include ELSE statements, but im not sure how or if it will even work

Example attached: Corporate Action History Master.xlsm


Below is an extract where it errors

Any thoughts please


Private Sub Worksheet_Change(ByVal Target As Range)

                
       For i = Cells(Rows.Count, "A").End(xlUp).Row To 7 Step -1
      If Cells(i, "I") = "Yes" And Cells(i, "D") = "FIRST NOTIFICATION (AUTO)" And Len(Cells(i, "K") > 5) And Len(Cells(i, "L") > 5) Then
       Sheets("Outstanding").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow.Value = Cells(i, 1).EntireRow.Value
        Sheets("Outstanding").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).FormatConditions.Delete
        Cells(i, 1).EntireRow.ClearContents
        MsgBox ("Transfered to outstanding")
        End If
        Next i
        
        For i = Cells(Rows.Count, "A").End(xlUp).Row To 7 Step -1
        If Cells(i, "M") = "Yes" Then
         Sheets("Daily Archive").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).EntireRow.Value = Cells(i, 1).EntireRow.Value
         Sheets("Daily Archive").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).FormatConditions.Delete
         Cells(i, 1).EntireRow.ClearContents
         MsgBox ("moved to archive on exit")
        End If
        Next i
                    
        
End Sub