Private Sub WorkSheet_Activate()
'Feuil1 et Feuil2 sont les CodeNames des 2 feuilles sources
Dim d1 As Object, d2 As Object, t, i&, rest(), e, P As Range
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
t = Feuil1.[A1].CurrentRegion.Resize(, 2) 'au moins 2 éléments
For i = 2 To UBound(t)
If t(i, 1) <> "" Then d1(t(i, 1)) = ""
Next
t = Feuil2.[A1].CurrentRegion.Resize(, 2) 'au moins 2 éléments
For i = 2 To UBound(t)
If t(i, 1) <> "" Then d2(t(i, 1)) = ""
Next
ReDim rest(1 To Application.Max(1 + d1.Count, [A1].CurrentRegion.Rows.Count), 1 To 1)
i = 1
rest(1, 1) = [A1] 'titre
If d1.Count Then
For Each e In d1.keys
If d2.exists(e) Then i = i + 1: rest(i, 1) = e
Next
End If
'---mémorisation des couleurs---
Set P = [A1].CurrentRegion.Resize(, 1)
t = P.Resize(, 2) 'au moins 2 éléments
d1.RemoveAll
For i = 1 To UBound(t)
If t(i, 1) <> "" Then
With P(i).Interior
If .ColorIndex <> xlNone Then d1(t(i, 1)) = .Color
End With
End If
Next
'---restitution des valeurs et couleurs---
Application.ScreenUpdating = False
Set P = P.Resize(UBound(rest))
P = rest
P.EntireColumn.Interior.ColorIndex = xlNone 'RAZ
For i = 1 To UBound(rest)
If d1.exists(rest(i, 1)) Then P(i).Interior.Color = d1(rest(i, 1))
Next
'---bordures---
P.EntireColumn.Borders.LineStyle = xlNone 'RAZ
[A1].CurrentRegion.Resize(, 1).Borders.Weight = xlThin
With Me.UsedRange: End With 'actualise la barre de défilement verticale
End Sub