1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
| Sub Formats()
'Formatage des données
'Fige l'écran pendant l'éxécution de la macro
Application.ScreenUpdating = False
'Envoie sur la page des données
Sheets("COMPTES").Activate
Dim n 'OK ---- Déclare la variable n pour dénombrer les colonnes choisies dans la procédure de formatage de dates
On Error GoTo formats_Error 'OK ---- Si erreur goto message erreur
With Sheets("COMPTES")
'Format century Gothic sur toute la BDD
With .Range("A2:R" & .Range("A" & Rows.Count).End(xlUp).Row).Font
.Name = "Century Gothic"
'Taille Caractére 8 sur toute la BDD
.Size = 8
End With
'Alignement Gauche de certaines colonnes
'For Each n In Array(4, 5, 6, 7, 8, 9, 11, 12)
'.Columns(n).HorizontalAlignment = xlLeft
'Next n
.Range("D:I,K:L").HorizontalAlignment = xlCenter
'Alignement Centre de certaines colonnes
'For Each n In Array(1, 2, 3, 10, 13, 14)
'.Columns(n).HorizontalAlignment = xlCenter
'Next n
.Range("A:C,J:J,M:N").HorizontalAlignment = xlCenter
'Alignement Centre de certaines colonnes
'For Each n In Array(1, 2, 3, 10, 13, 14)
'.Columns(n).HorizontalAlignment = xlCenter
'Next n
''Format € de certaines colonnes
'For Each n In Array(15, 16, 17, 18)
'.Columns(n).NumberFormat = "#,##0.00 €"
'Next n
.Range("O:R").EntireColumn.NumberFormat = "#,##0.00 €"
'Alignement Droite de certaines colonnes
'For Each n In Array(15, 16, 17, 18)
'.Columns(n).HorizontalAlignment = xlRight
'Next n
.Range("O:R").EntireColumn.HorizontalAlignment = xlRight
'Format des titres de colonnes
With .Range("A1:R1")
.Font.Name = "Century Gothic"
.Font.Size = 10
.Interior.ColorIndex = 43
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.NumberFormat = "General"
End With
'Ajustement automatique des colonnes
'For Each n In Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, _
' 18)
'.Columns(n).Columns.AutoFit
.Range("A:A,J:J").ColumnWidth = 8
.Range("B:D,F:F,M:M").ColumnWidth = 11
.Range("E:E").ColumnWidth = 16
.Range("G:G").ColumnWidth = 33
.Range("H:H,O:R").ColumnWidth = 12
.Range("I:I").ColumnWidth = 25
.Range("K:K").ColumnWidth = 34
.Range("L:L").ColumnWidth = 13
.Range("N:N").ColumnWidth = 7
'Next n
'---Colonne A:C---CODE----
'Format "General" la colonne A
.Range("A2:C" & .Range("A" & Rows.Count).End(xlUp).Row).NumberFormat = _
"General"
'---Colonne B---DATE---
'Choix du format des dates
.Range("B2:B" & .Range("A" & Rows.Count).End(xlUp).Row).NumberFormat = _
"dd/mm/yyyy"
'---Colonne C---ANNEE---
'Format "General" la colonne c
' .Range("C2:C" & .Range("A" & Rows.Count).End(xlUp).Row).NumberFormat = _
' "General"
'---Toute La PLAGE DE DONNEES----
With .Range("a1").CurrentRegion
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline: End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline: End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline: End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline: End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline: End With
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlHairline: End With
End With
'Renvoie sur la Feuille de Synthése
Sheets("SYNTHESE").Activate
'Défige l'écran aprés l'éxécution de la macro
Application.ScreenUpdating = True
End With
On Error GoTo 0
Exit Sub
formats_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure formats of Module Formats" 'OK ---- Message d'erreur
End Sub |
