Private Sub Worksheet_Change(ByVal Target As Range)
Dim P As Range, n As Byte, derlig As Long, sel As Range
Set P = Range("A:S,U:AG,AI:AN") 'les 3 tableaux
Application.EnableEvents = False
For Each P In P.Areas
n = n + 1
If Not Intersect(Target, P) Is Nothing Then
Application.ScreenUpdating = False
P.FormatConditions.Delete 'suppression de toute MFC
derlig = P.Find("*", , xlValues, , xlByRows, xlPrevious).Row
If derlig > 4 Then
P.Rows(4).AutoFill P.Rows("4:" & derlig), xlFillFormats
End If
With P.Rows("3:" & derlig)
If n = 1 Then
Set sel = Selection 'mémorise
.Cells(1).Select
.FormatConditions.Add xlExpression, , "=ET(COLONNE()>3;N(A3)>3)"
sel.Select
With .FormatConditions(1)
.Interior.ColorIndex = 7 'rose
.Font.Bold = True
.Font.ColorIndex = 5 'bleu
End With
End If
.FormatConditions.Add xlExpression, , "=MOD(LIGNE();2)"
.FormatConditions(IIf(n = 1, 2, 1)).Interior.ColorIndex = 15 'gris
End With
P.Rows(derlig).Borders(xlEdgeBottom).Weight = xlThick
P.Rows(derlig).Borders(xlEdgeBottom).ColorIndex = xlAutomatic
P.Rows(derlig + 1 & ":" & Rows.Count).ClearFormats 'RAZ en dessous
End If
Next
Application.EnableEvents = True
End Sub