Worksheet Change event, if content of cell is cleared, then clear the cell next to it

Question

The first part of the code works; when the user changes the cell to ACC, it inputs the right values. However, if the user deletes it, then I want the comment and the content of the cell next to it to clear as well. Right now, when I delete it, it won't change anything. I tried Target.Value = "" and it doesn't work neither.

Sub worksheet_change(ByVal Target As Range)

    Dim WatchRange As Range
    Dim IntersectRange As Range
    Dim lastrow As Long

    'If the cell in row J changes either ACC
        lastrow = Range("A" & Rows.Count).End(xlUp).Row

        Set WatchRange = Range("J9:J" & lastrow)
        Set IntersectRange = Intersect(Target, WatchRange)

        If Target.Count <> 1 Then Exit Sub 'makes sure only one cell is selected

            If Not (IntersectRange Is Nothing) And (Target = "ACC") Then 'if changed to ACC

                Application.EnableEvents = False

                    'Then input what the Poste Recherché was, date time and user
                    Cells(Target.Row, 11).ClearComments
                    Cells(Target.Row, 11).AddComment Application.UserName & vbNewLine & Now & _
                    vbNewLine & "Quart: " & Range("$F$3") & vbNewLine & "Date: " & Range("$F$4")
                    Cells(Target.Row, 11).Value = "ACCEPTÉ: " & Range("$F$2")

                Application.EnableEvents = True

            End If

            If Not IntersectRange Is Nothing And Target Is Empty Then

                Application.EnableEvents = False

                    Cells(Target.Row, 11).ClearComments
                    Cells(Target.Row, 11).ClearContents

                Application.EnableEvents = True

            End If    

End Sub

Since you are working with Worksheet_Change, and I can see couple of things which are wrong, I would recommend going through [THIS](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) — Siddharth Rout, Mar 04 '20 at 16:40

score 1 · Accepted Answer · answered Mar 04 '20 at 16:05

1

replace:

Target Is Empty

with:

Target = ""

answered Mar 04 '20 at 16:05

Gary's Student

95,722
10
59
99

Or `IsEmpty(Target.Value)`. – BigBen Mar 04 '20 at 16:06

Worksheet Change event, if content of cell is cleared, then clear the cell next to it

1 Answers1