Sub commentaires()
Dim comm As String
Cells.ClearComments ' efface les commentaires
Set plage = Range("B6:B27")
Set plage = Application.Union(plage, Range("G6:G30"))
Set plage = Application.Union(plage, Range("L6:L41"))
Set plage = Application.Union(plage, Range("Q6:Q39"))
tablo = Sheets("A").Range("B2:G" & Sheets("A").Range("B200").End(xlUp).Row)
For Each cel In plage
If InStr(cel.Value, Chr(10)) <> 0 Then
acomp = Split(cel.Value, Chr(10))(0)
Else
acomp = cel.Value
End If
For n = LBound(tablo, 1) To UBound(tablo, 1)
If tablo(n, 1) = acomp Then
comm = comm & "debut: " & Format(tablo(n, 3), "hh:mm") & Chr(10) & "fin: " & Format(tablo(n, 5), "hh:mm") _
& Chr(10) & "" & Chr(10) & "" & (tablo(n, 6)) & Chr(10)
End If
Next
cel.ClearComments
cel.AddComment comm
If Len(comm) > 30 Then
cel.Comment.Shape.Height = cel.Comment.Shape.Height * 1.2
Else
cel.Comment.Shape.Height = cel.Comment.Shape.Height * 0.8
End If
comm = ""
Next
End Sub