Microsoft 365 effacer commentaire

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim MyValue As Variant
  Dim r As Range
  Dim cell As Range, fd As Range
If Intersect(Range("B:B"), Target) Then
  Set r = Sheets("1").Range("B:B")
  Set fd = r.Find(Cells(Target.Row, 1).Value, LookIn:=xlValues)
  If Not fd Is Nothing Then
    Sheets("1").Cells(fd.Row, 2) = ""
    Sheets("1").Cells(fd.Row, 3) = ""
    Sheets("1").Cells(fd.Row, 4) = ""
  End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DerLig&, NewValue, OldValue, i&
DerLig = Worksheets("Recap").Range("A" & Rows.Count).End(xlUp).Row
If Not Application.Intersect(Target, Range("B7:B" & DerLig)) Is Nothing Then
    With Target
        Application.EnableEvents = False
        NewValue = .Value
        Application.Undo
        OldValue = .Value
        .Value = NewValue
        If OldValue <> NewValue Then
            ChercheNom = Target.Offset(, -1)
            For i = 3 To 10
                If Worksheets("1").Range("B" & i).Value = ChercheNom Then
                    Worksheets("1").Range("D" & i).Value = ""
                    '******** Les 2 lignes suivantes pour sortir des que le 1er client est trouvé
                    '******** Sinon cela supprimera TOUS les commentaires du même client
                    Application.EnableEvents = True
                    Exit Sub
                    '*********
                End If
            Next i
        End If
        Application.EnableEvents = True
    End With
End If
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Val(Sh.Name) = 0 Then Exit Sub
Dim derlig&, i&, x$, y$, j, z$
derlig = Sh.Cells.SpecialCells(xlCellTypeLastCell).Row
Application.EnableEvents = False
If Sh.Cells(3, "AB") <> "" Then
    Sh.Range("D3:D" & Sh.Rows.Count).ClearContents 'RAZ
    For i = 3 To derlig
        x = Sh.Cells(i, "B")
        If x = "" Then Exit For
        For j = 3 To derlig
            y = Sh.Cells(j, "AB")
            If y = "" Then Exit For
            If y = x And Sh.Cells(i, "D") = "" Then Sh.Cells(i, "D") = Sh.Cells(j, "AD"): Exit For
    Next j, i
End If
'---stockage---
Sh.Range("AB3:AD" & derlig) = Sh.Range("B3:D" & derlig).Value 'plage à adapter
Application.EnableEvents = True
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
If Val(Sh.Name) Then Sh.[AB:AD].ClearContents
Workbook_SheetActivate Sh
Application.EnableEvents = True
End Sub