Sub Grille()
If ActiveSheet.Name <> "Feuil2" Then Exit Sub 'sécurité
Dim plage As Range, n&, cel As Range, i&, v, v1
Dim flag As Boolean, ref As Range
Application.ScreenUpdating = False
'---définition de la plage de base----
Set plage = Union([C2].Resize(, [IV2].End(xlToLeft).Column - 3), _
[C4].Resize(, [IV4].End(xlToLeft).Column - 3), _
[C6].Resize(, [IV6].End(xlToLeft).Column - 3))
n = Application.Count(plage) 'nombre de valeurs
'---remplissage de la grille---
[C12:G200].Clear
For Each cel In [C12:G200]
1 i = i + 1
v = Application.Large(plage, i) 'fonction GRANDE.VALEUR
If i < n Then
v1 = Application.Large(plage, i + 1)
If v = v1 Then flag = True: GoTo 1 'si doublon
End If
Set ref = plage.Find(v, LookIn:=xlFormulas, LookAt:=xlWhole)
ref.Copy cel 'copie de la cellule trouvée
If flag Then 'traitement du doublon
cel.FormatConditions.Delete 'suppression de la MFC
cel.Interior.ColorIndex = 3 'couleur rouge
flag = False
End If
cel.AddComment 'création du commentaire
cel.Comment.Text ref.Offset(1).Text
If i = n Then Exit For
Next
End Sub