Using Worksheet_Change to clear contents due to change in specific cell, but not working

Question

I want the following code to be activated when there is any change in cell C8. When a change occurs the ranges below should be cleared. It is almost working but for some reason the only time this code won't run is if I delete the content (meaning I go to the cell and press "Delete" on my keyboard). Is there a way to fix this and or cover this instance?

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("C8").Address Then
        Resp = MsgBox("You are changing the job number. Doing so will clear your responses and the peer reviewer's responses." & vbCrLf & vbCrLf & _
          "Are you sure you want to proceed" & vbCrLf & _
          "by clearing all those cells?", 36, "Please confirm !!!!")
    
        If Resp <> 6 Then
            'No was pressed, here's the message telling them action is cancelled
            MsgBox "No problem," & vbCrLf & _
              "please click OK, no harm no foul.", 48, "Action cancelled."  ' hello
    
        Else
    
           'Range("J26").ClearContents
           Range("C22:C85").ClearContents
           Range("C18:F18").Value = ""
           Range("C17:F17").Value = ""
           Range("C16:F16").Value = ""
           Range("C15:F15").Value = ""
           Range("C14:F14").Value = ""
           Range("C13:F13").Value = ""
           Range("C12:F12").Value = ""
           Range("C11:F11").Value = ""
           Range("C10:F10").Value = ""
           Range("C9:F9").Value = ""
           Range("C22:C85").ClearContents
                  
           MsgBox "Job Changed! Cleared Responses."
        End If
    End If
End Sub

Answer 1

You'll get a compile error if you try VBE debug -> Complile VBAproject before running the code. There are two If clauses, but only one End If in your code.

Fix:

• Add End If at the end
• Using EnableEvents = False disables events before clearing cells so that the Change event is not triggered by itself.
• Clearing C9:F28 can be done with a code line.

Microsoft documentation:

Application.EnableEvents property (Excel)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Resp
    If Target.Address = Range("C8").Address Then
        Resp = MsgBox("You are changing the job number. Doing so will clear your responses and the peer reviewer's responses." _
            & vbCrLf & vbCrLf & "Are you sure you want to proceed" & vbCrLf & _
            "by clearing all those cells?", 36, "Please confirm !!!!")
        If Resp <> 6 Then
            'No was pressed, here's the message telling them action is cancelled
            MsgBox "No problem," & vbCrLf & "please click OK, no harm no foul.", _
                48, "Action cancelled."  ' hello
        Else
            Application.EnableEvents = False
            'Range("J26").ClearContents
            Range("C9:F28").ClearContents
            Range("C22:C85").ClearContents
            Application.EnableEvents = True
            MsgBox "Job Changed! Cleared Responses."
        End If
    End If
End Sub