GADENSEB
XLDnaute Impliqué
Re bonjour le Forum,
Je cherche à simplifier ces macros dans le module 2
Qui peut m'aider, svp ?
Bonne aprem.
Seb
'======= DEFINIT LA ZONE D'IMPRESSION =============
'============ CHANGE LA COULEUR DES COLONNES SOMMAIRES =========
Je cherche à simplifier ces macros dans le module 2
Qui peut m'aider, svp ?
Bonne aprem.
Seb
Code:
Sub CONVERTIRFORMATS()
'Convertir en format DATE
Worksheets("BASE EMPLOI").Select
Range("T65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("T2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
Range("U65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("U2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
Range("AB65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AB2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
Range("AJ65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AJ2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
Range("AK65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AK2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
Range("AL65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AL2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
Range("AM65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AM2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
Range("AT65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("AT2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
Range("BB65536").End(xlUp).Select
Selection.TextToColumns Destination:=Range("BB2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "dd/mm/yy"
End Sub
Code:
Sub CONVERTIRDATES()
Worksheets("BASE EMPLOI").Select
Range("T2:T1500").Select
Selection.TextToColumns Destination:=Range("T2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Range("U2:U1500").Select
Selection.TextToColumns Destination:=Range("U2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Range("AB2:AB1500").Select
Selection.TextToColumns Destination:=Range("AB2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Range("AJ2:AJ1500").Select
Selection.TextToColumns Destination:=Range("AJ2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Range("AK2:AK1500").Select
Selection.TextToColumns Destination:=Range("AK2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Range("AL2:AL1500").Select
Selection.TextToColumns Destination:=Range("AL2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Range("AM2:AM1500").Select
Selection.TextToColumns Destination:=Range("AM2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Range("AU2:AU1500").Select
Selection.TextToColumns Destination:=Range("AT2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
Range("BB2:BB1500").Select
Selection.TextToColumns Destination:=Range("BA2"), DataType:=xlFixedWidth, _
FieldInfo:=Array(0, 4), TrailingMinusNumbers:=True
End Sub
'======= DEFINIT LA ZONE D'IMPRESSION =============
Code:
Sub ZONEIMPRESSION()
Worksheets("BASE EMPLOI").Select
'Détermine la zone d'impression
ActiveSheet.PageSetup.PrintArea = Range("A1:BB" & _
Range("A65536").End(xlUp).Row).Address
End Sub
Code:
Sub COULEURCOLONNES()
Worksheets("BASE EMPLOI").Select
' Trait du bas sur toutes les lignes
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Range("A2").Select
' Pas de trait sur les colonnes sommaires
Range("F:F").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("M:M").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("S:S").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("Z:Z").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("AE:AE").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("AR:AR").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("A1").Select
' Copie les couleurs des colonnes
Range("F3").Select
Selection.AutoFill Destination:=Range("F3:F1500"), Type:=xlFillDefault
Range("F3:F1500").Select
Range("M3").Select
Selection.AutoFill Destination:=Range("M3:M1500"), Type:=xlFillDefault
Range("M3:M1500").End(xlUp).Select
Range("S3").Select
Selection.AutoFill Destination:=Range("S3:S1500"), Type:=xlFillDefault
Range("S3:S1500").End(xlUp).Select
Range("Z3").Select
Selection.AutoFill Destination:=Range("Z3:Z1500"), Type:=xlFillDefault
Range("Z3:Z1500").End(xlUp).Select
Range("AE3").Select
Selection.AutoFill Destination:=Range("AE3:AE1500"), Type:=xlFillDefault
Range("AE3:AE1500").End(xlUp).Select
Range("AR3").Select
Selection.AutoFill Destination:=Range("AR3:AR1500"), Type:=xlFillDefault
Range("AR3:AR1500").End(xlUp).Select
Range("A2").Select
End Sub
Code:
Sub CENTURYGOTHIC8()
Worksheets("BASE EMPLOI").Select
'Met en Century Gothics 8
Cells.Select
With Selection.Font
.Name = "Century Gothic"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Century Gothic"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("B2").Select
End Sub
Code:
Sub MFCATRAITER()
' Mise en forme conditionnelle "A TRAITER"
Worksheets("BASE EMPLOI").Select
ActiveWindow.SmallScroll Down:=-12
Range("B65536").End(xlUp).Select
Selection.FormatConditions.Add Type:=xlTextString, String:="A TRAITER", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlPatternLinearGradient
.Gradient.Degree = 90
.Gradient.ColorStops.Clear
End With
With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0)
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(0.5)
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior.Gradient.ColorStops.Add(1)
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub