Sub Comparaison()
Dim Cel1 As Range, Cel2 As Range, Modif As Boolean
Dim F1 As Worksheet, F2 As Worksheet
Dim I As Integer
Set F1 = Sheets(Sheets.Count)
Set F2 = Sheets(Sheets.Count - 1)
Application.ScreenUpdating = False
With F1
For Each Cel1 In .Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeConstants)
Set Cel2 = F2.Columns(1).Find(Cel1, LookIn:=xlValues, lookat:=xlWhole)
If Cel2 Is Nothing Then
Cel1.Offset(0, 3) = "Nouvelle ligne"
Else
Modif = False
For I = 1 To 2
If Cel1.Offset(0, I).Value <> Cel2.Offset(0, I) Then
Modif = True
Cel1.Offset(0, I).Font.ColorIndex = 3
Else
Cel1.Offset(0, I).Font.ColorIndex = 1
End If
Next
Cel1.Offset(0, 3) = IIf(Modif = True, "Ligne modifiée", "Ligne inchangée")
End If
Next
.Range("E1") = WorksheetFunction.CountIf(.Columns(4), "Ligne inchangée") / .Columns(4).SpecialCells(xlCellTypeConstants).Count
End With
Application.ScreenUpdating = True
End Sub