Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A:A]) Is Nothing Then Exit Sub
Dim c As Range, p%, dat As Long, i As Variant
Application.ScreenUpdating = False
'---mémorisation des commentaires---
For Each c In [A1].CurrentRegion.Resize(, 1)
If Not c.Comment Is Nothing Then
p = InStr(c.Comment.Text & " ", " ")
If IsDate(Left(c.Comment.Text, p - 1)) Then _
If Application.CountIf(Columns("H"), c.Comment.Text) = 0 _
Then Cells(Rows.Count, "H").End(xlUp)(2) = c.Comment.Text
c.ClearComments
End If
Next
'---restitution des commentaires aux bonnes dates ---
For Each c In [H1].CurrentRegion.Offset(1)
If c <> "" Then
p = InStr(c & " ", " ")
dat = CDbl(CDate(Left(c, p - 1)))
i = Application.Match(dat, Columns(1), 0)
If IsNumeric(i) Then
With Cells(i, 1).AddComment
.Text c.Text
.Shape.TextFrame.AutoSize = True
.Visible = True
End With
End If
End If
Next
End Sub