Sub doublonsChampCmt()
couleurs = Array(0, 1, 3, 4, 7, 8, 14, 15, 16, 17, 21, 22, 23, 25, 27, 28, 33, 34, 35, 36, 37, 38, 39, 40, 42, 43, 44, 45, 46, 50, 53)
Set champ = Range("A1:W100")
champ.Interior.ColorIndex = xlNone
champ.ClearComments
Set d = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
For Each c In champ
If c.Value <> "" Then
d.Item(c.Value) = d.Item(c.Value) + 1
d2.Item(c.Value) = _
d2.Item(c.Value) & c.Address & " "
End If
Next c
For Each c In champ
If d.Item(c.Value) > 1 Then
nocoul = (Application.Match(c.Value, d.keys, 0)) Mod UBound(couleurs)
c.Interior.ColorIndex = couleurs(nocoul)
c.AddComment
temp = c.Value
c.Comment.Text Text:=Left(d2.Item(temp), Len(d2.Item(temp)) - 1)
c.Comment.Shape.Left = c.Offset(, 1).Left + 10
c.Comment.Shape.TextFrame.AutoSize = True
c.Comment.Visible = True
End If
Next c
End Sub