Private Sub Worksheet_Activate()
[A1:M100].ClearContents
[A2:M100].Interior.ColorIndex = xlNone
[couleurs].Copy
[B1].PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
lignedep = 2
ligne = 2
For i = 1 To 20
Cells(ligne, 1) = Sheets("plansem1").[A4].Offset(i - 1)
For Each s In Array("plansem1", "plansem2")
Set f = Sheets(s)
For Each c In f.[b4].Offset(i - 1).Resize(, 190)
If c <> "" Then
col = [1:1].Find(c).Column
lg = lignedep: témoin = False
Do While lg <= ligne And Not témoin
If Cells(lg, col) = "" Then
Cells(lg, col) = f.Cells(2, c.Column)
Cells(lg, col).Interior.ColorIndex = Cells(1, col).Interior.ColorIndex
témoin = True
Else
lg = lg + 1
End If
Loop
If Not témoin Then
ligne = ligne + 1
Cells(ligne, col) = f.Cells(2, c.Column)
Cells(ligne, col).Interior.ColorIndex = Cells(1, col).Interior.ColorIndex
End If
End If
Next c
Next s
ligne = ligne + 2
lignedep = ligne
Next i
Application.Calculation = xlAutomatic
End Sub