Excel Code not running when edits are made-CodePudding

I'm trying to get my spreadsheet to update when an edit is made in row "L" but for some reason when I edit the cells within my range nothing changes, it was working perfectly before but now it doesn't

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Me.Range("L100:L200")) Is Nothing Then
    Call CopyDataTesting

End If

Application.EnableEvents = True

End Sub

Can someone help me figure out what's wrong. Column I am trying to edit on successful macro run, the Pass or Fail should become all capitalized

Edit: Here is the CopyTestData Code:

Sub CopyDataTesting()
    Dim c As Range
   
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    
    For Each c In Range("L1:L" & Cells(Rows.Count, "L").End(xlUp).Row)
        
        SN = c.Offset(0, -3).Value
        
        If c = "Fail" Then
            c.Offset(1, 0).EntireRow.Insert
            c.EntireRow.Copy
            c.Offset(1, 0).EntireRow.PasteSpecial Paste:=xlPasteValues
            c.Select
            c.Offset(1, 0).ClearContents
            c.Offset(0, 2).Interior.Color = RGB(0, 0, 0)
            c.Offset(0, 3).Interior.Color = RGB(0, 0, 0)
            c.Offset(0, 4).Interior.Color = RGB(0, 0, 0)
            c.Offset(0, 5).Interior.Color = RGB(0, 0, 0)
            c.Offset(0, 6).Interior.Color = RGB(0, 0, 0)
            c.Offset(0, 7).Interior.Color = RGB(0, 0, 0)
            c.Value = "FAIL"
            c.Offset(1, 0).Select
        ElseIf c = "Pass" Then
            c.Value = "PASS"
            
            c.Offset(0, 2).Interior.Color = RGB(208, 206, 206)
            c.Offset(0, 3).Interior.Color = RGB(208, 206, 206)
            c.Offset(0, 4).Interior.Color = RGB(208, 206, 206)
            c.Offset(0, 5).Interior.Color = RGB(240, 202, 237)
            c.Offset(0, 6).Interior.Color = RGB(240, 202, 237)
            c.Offset(0, 7).Interior.Color = RGB(240, 202, 237)
            
                    
        End If
       
    Next
    
End Sub

Edit 2:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = False

    If Not Intersect(Target, Range("L1:L1000")) Is Nothing Then
        Call CopyDataTesting
    ElseIf Not Intersect(Target, Range("R1:R100")) Is Nothing Then
        Call CopyDataFinal 'Assume that this macro and the CopyDataTesting function similarly

    End If
    
Application.EnableEvents = True


End Sub

CodePudding user response：

Note it's good practice to add error handling whenever you turn off events, to make sure they get re-enabled before your code exits.

Also the event code can determine exactly which cells in Col L were changed and pass that range to CopyDataTesting, so there's no need to scan the whole column each time.

This works fine for me:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim rng As Range
    
    On Error GoTo haveError
    Application.EnableEvents = False
    
    Set rng = Application.Intersect(Target, Me.Range("L1:L1000"))
    If Not rng Is Nothing Then
        CopyDataTesting rng 'pass the changed cells to `CopyDatatesting`
    End If
    
    Set rng = Application.Intersect(Target, Me.Range("R1:R1000"))
    If Not rng Is Nothing Then
        CopyDataFinal rng  'pass the changed cells to `CopyDataFinal`
    End If
    
haveError:
    'ensure events are re-enabled
    Application.EnableEvents = True
End Sub

Sub CopyDataFinal(rng As Range)
    'add your code here
End Sub

Sub CopyDataTesting(rng As Range)
    Dim c As Range, SN
   
    Application.ScreenUpdating = False
    For Each c In rng.Cells
        SN = c.Offset(0, -3).Value  '<<<<< what is this for?
        If c = "Fail" Then
            c.Offset(1, 0).EntireRow.Insert
            c.EntireRow.Offset(1).Value = c.EntireRow.Value
            c.Offset(1, 0).ClearContents
            c.Offset(0, 2).Resize(1, 6).Interior.Color = RGB(0, 0, 0)
            c.Value = "FAIL"
        ElseIf c = "Pass" Then
            c.Offset(0, 2).Resize(1, 3).Interior.Color = RGB(208, 206, 206)
            c.Offset(0, 5).Resize(1, 3).Interior.Color = RGB(240, 202, 237)
            c.Value = "PASS"
        End If
    Next c
End Sub