Sub form()
Dim lig As Integer, plage As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual ' accélère l'exécution
Application.DisplayAlerts = False
For lig = 1 To 100
With Range(Cells(lig, "A"), Cells(lig, "P"))
'si la ligne n'est pas vide on l'unit à plage
If Application.CountA(.Cells) Then _
Set plage = Union(IIf(plage Is Nothing, .Cells, plage), .Cells)
End With
Range(Cells(lig, "M"), Cells(lig, "P")).Select 'fusionne MNOP
Selection.Merge
Selection.EntireRow.AutoFit ' hauteur ligne automatique
Selection.EntireRow.RowHeight = Selection.EntireRow.RowHeight + 10 ' + 10 pixel
If Selection.EntireRow.RowHeight < 25 Then Selection.EntireRow.RowHeight = 25 ' hauteur de ligne 25 mini
Next
'efface la MFC et les bordures
With Range("A1😛100")
.FormatConditions.Delete
.Borders(xlLeft).LineStyle = xlNone
.Borders(xlRight).LineStyle = xlNone
.Borders(xlTop).LineStyle = xlNone
.Borders(xlBottom).LineStyle = xlNone
'.Interior.ColorIndex = 43
End With
If plage Is Nothing Then Exit Sub 'si tout est vide...
With plage
'crée les bordures
.Borders(xlLeft).LineStyle = xlContinuous
.Borders(xlRight).LineStyle = xlContinuous
.Borders(xlTop).LineStyle = xlContinuous
.Borders(xlBottom).LineStyle = xlContinuous
'.Interior.ColorIndex = 43
.HorizontalAlignment = xlLeft 'alignement texte
.VerticalAlignment = xlCenter
.Locked = False 'protection cellule
.FormulaHidden = False
'MFC 1ère condition
.FormatConditions.Add Type:=xlExpression, Formula1:="=CELLULE(""row"")=LIGNE()"
With .FormatConditions(1)
.Font.Bold = True
.Font.Italic = False
.Font.ColorIndex = 3
.Interior.ColorIndex = 6
End With
'MFC 2ème condition
.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(LIGNE();2)=0"
.FormatConditions(2).Interior.ColorIndex = 34
'MFC 3ème condition
.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(LIGNE();1)=0"
.FormatConditions(3).Interior.ColorIndex = 36
End With
Application.Calculation = xlAutomatic
Application.DisplayAlerts = True
End Sub