Sub compare()
Sheets("Feuil3").Cells.Clear
Set dico = CreateObject("Scripting.dictionary")
For n = 1 To 2
For m = 1 To Sheets(n).Range("c" & Rows.Count).End(xlUp).Row
x = Trim(Sheets(n).Range("c" & m)) & " " & Trim(Replace(Sheets(n).Range("b" & m), " ", "_")) & " " & Trim(Sheets(n).Range("a" & m))
dico(x) = dico(x) & Sheets(n).Name & "!"
Next
Next
c = dico.keys
b = dico.items
a = dico.items
For n = LBound(c) To UBound(c)
Sheets("Feuil3").Cells(n + 1, 3) = Split(c(n))(0)
Sheets("Feuil3").Cells(n + 1, 2) = Split(c(n))(1)
Sheets("Feuil3").Cells(n + 1, 1) = Split(c(n))(2)
y = Left(b(n), Len(b(n)) - 1)
If UBound(Split(y, "!")) > 0 Then
Sheets("Feuil3").Cells(n + 1, 4) = "commun"
Else
If y = Sheets(1).Name Then
Sheets("Feuil3").Cells(n + 1, 4) = "disparu"
Sheets("Feuil3").Cells(n + 1, 4).Interior.ColorIndex = 4
Else
Sheets("Feuil3").Cells(n + 1, 4) = "nouveau"
Sheets("Feuil3").Cells(n + 1, 4).Interior.ColorIndex = 5
End If
End If
Next
End Sub