'### Constantes à adapter (noms des feuilles) ###
Const BASE As String = "Feuil2"
Const SAISIE As String = "Feuil1"
'################################################
Sub ComparePMO()
Dim WB As Workbook
Dim S1 As Worksheet
Dim S2 As Worksheet
Dim var1
Dim var2
Dim R2 As Range
Dim C As Range
Dim i&
Dim j&
Dim k&
Dim Col1
Col1 = Array(, 1, 2, 9, 4, 12, 6, 7, 10, 8, 11)
Set WB = ThisWorkbook
Set S1 = WB.Sheets(BASE)
var1 = S1.[a1].CurrentRegion
Set S2 = WB.Sheets(SAISIE)
Set R2 = S2.Range("a2:j" & S2.[b65536].End(xlUp).Row & "")
var2 = R2
For i& = 1 To UBound(var2, 1)
For j& = 1 To UBound(var1, 1)
If UCase(var2(i&, 2)) = UCase(var1(j&, 2)) And _
UCase(var2(i&, 4)) = UCase(var1(j&, 4)) Then
For k& = 1 To 10
var2(i&, k&) = var1(j&, Col1(k&))
If k& = 1 Then
If var2(i&, k&) = "H" Then
var2(i&, k&) = "M."
Else
var2(i&, k&) = "Mme"
End If
End If
Next k&
Exit For
End If
Next j&
Next i&
R2 = var2
For Each C In R2.Range("a1:a" & UBound(var2, 1) & "")
If C = "" Then
C.Resize(1, 10).Interior.Color = vbRed
End If
Next C
End Sub