Reporter mise en forme par VBA sur lignes non vides

isa44 · 17 Janvier 2011

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.

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

Effectuez une recherche sur Excel Downloads...

Reporter mise en forme par VBA sur lignes non vides

isa44

XLDnaute Occasionnel

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums