Clear contents in one cell when contents of another cell are deleted

[rlbush2]

New to the forum and have a quick VBA question.

The specified target cell is a drop down validation box with text. If the user deletes the text in the target cell, I want to clear the formula in the corresponding cell that is 7 columns to the right. Here is the code I have now that does not work:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
   If Target.Value = Cells(Rows.Count, "J").End(xlUp).Value Then
  
           On Error Resume Next
                Application.EnableEvents = False
        Dim rRange As Range
        Dim rRange2 As Range
        Set rRange = Target.Offset(0, 7)
        Set rRange2 = Target.Offset(-1, 7)
        rRange2.Copy
        rRange.PasteSpecial (xlPasteAll)
        Application.CutCopyMode = False
                Application.EnableEvents = True
                         On Error GoTo 0
        Else
             rRange.ClearContents
End If
    
End Sub

What changes do I need to make?

Any help much appreciated - Bob B

[FDibbins]

why dont you do this with a regulat formula? something like =if(target cell ="","",your-original-formula)

[jeffreybrown]

Not sure what you have going on with the copy stuff, but for the clearing 7 cells to the right when column J target is deleted...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 10 Then Exit Sub
    If Not Intersect(Target, Range("J:J")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = "" Then Target.Offset(, 7).ClearContents
        Application.EnableEvents = True
    End If
End Sub