Bonjour et meilleurs voeux à tous.
J'ai enregitré ce code que je voudrais modifier pour qu'il s'applique sur toutes les lignes dont les cellules en L ne sont pas vides( de la ligne 63 à 163).
Merci pour votre précieuse aide.
J'ai enregitré ce code que je voudrais modifier pour qu'il s'applique sur toutes les lignes dont les cellules en L ne sont pas vides( de la ligne 63 à 163).
Merci pour votre précieuse aide.
Code:
Sub format()
'Supprime les formats conditionnels
Range("A63:P474").Select
With Selection
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.FormatConditions.Delete
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Range("L63").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=CELLULE(""row"")=LIGNE()"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
'.ColorIndex = 3
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ET($L63<>"""";MOD(LIGNE();2)=0)"
'Selection.FormatConditions(2).Font.ColorIndex = xlAutomatic
With Selection.FormatConditions(2).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(2).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$L63<>"""""
'Selection.FormatConditions(3).Font.ColorIndex = xlAutomatic
With Selection.FormatConditions(3).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(3).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(3).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(3).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
'"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Selection.FormatConditions(3).Interior.Pattern = xlNone ' copie du format sur la ligne
Selection.AutoFill Destination:=Range("L63:A63"), Type:=xlFillFormats
'Range("A63:L63").Select
'""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
Range("M63:P63").Select 'fusion MNOP
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
'""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
'met les formats conditionnels sur la cellule fusionnée MNOP
Range("M63:P63").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=CELLULE(""row"")=LIGNE()"
With Selection.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 3
End With
With Selection.FormatConditions(1).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(1).Interior.ColorIndex = 6
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ET($L63<>"""";MOD(LIGNE();2)=0)"
' Selection.FormatConditions(2).Font.ColorIndex = xlAutomatic
With Selection.FormatConditions(2).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(2).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(2).Interior.ColorIndex = 15
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$L63<>"""""
' Selection.FormatConditions(3).Font.ColorIndex = xlAutomatic
With Selection.FormatConditions(3).Borders(xlLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(3).Borders(xlRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(3).Borders(xlTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.FormatConditions(3).Borders(xlBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.FormatConditions(3).Interior.Pattern = xlNone
Rows("63:163").Select ' Hauteur de ligne
Selection.RowHeight = 25
End Sub
Dernière édition:
