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
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
| Sub generer_calendrier()
Dim tb(), Plg As Range, cel As Range, i As Integer
Dim Cmois, Cjour, Cvaleur, Ccouleur
Set Plg = Range("A3:X33") 'si ta plage reste fixe
i = 0
'ci-desous, un tableau qui reprend toutes les cellules non vide en B, D, E, etc.
'******************************
For Each cel In Plg
If WorksheetFunction.IsEven(cel.Column) And cel <> "" Then
i = i + 1: ReDim Preserve tb(1 To i)
tb(i) = Month(CDate(cel(1, 0))) & "|" & Day(CDate(cel(1, 0))) & "|" & cel & "|" & cel.Interior.Color
End If
Next cel
'***********************************
'ton programme que je ne touche pas
'ACCELERATION DU PROGRAMME
Application.ScreenUpdating = False
'INITIALISATION DU TABLEAU
Range("A3:X33").ClearContents
Range("A3:X33").ClearFormats
annee = TextBox_annee
'FORMAT DATE CELLULES
Range("E25,A:A,C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q,S:S,U:U,W:W").NumberFormat = "ddd dd"
'FORMAT DATE AUJOURD'HUI
Cells(1, 13).NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
'Boucle MOIS
For mois = 1 To 12
'NOMBRES DE JOURS CALCULER DANS LE MOIS
nb_jours = Day(DateSerial(annee, mois + 1, 1) - 1)
'SAUT DE COLONNES
colonne = mois * 2 - 1
'BOUCLE JOURS
For jour = 1 To nb_jours
date_du_jour = DateSerial(annee, mois, jour)
Cells(jour + 2, colonne) = date_du_jour
'TEST WEEKEND ET COULEURS
If Weekday(date_du_jour) = 1 Or Weekday(date_du_jour) = 7 Then
Cells(jour + 2, colonne).Interior.Color = RGB(150, 200, 240)
Cells(jour + 2, colonne + 1).Interior.Color = RGB(150, 200, 240)
Else
Cells(jour + 2, colonne).Interior.Color = RGB(255, 255, 255)
Cells(jour + 2, colonne + 1).Interior.Color = RGB(255, 255, 255)
End If
'--------------------------------------BORDURES------------------------------------------------------
'GAUCHES
With Cells(jour + 2, colonne).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With Cells(jour + 2, colonne).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With Cells(jour + 2, colonne).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
If Weekday(date_du_jour) = 1 Then ' si dimanche
With Cells(jour + 2, colonne).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
Else
With Cells(jour + 2, colonne).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
End If
If jour = nb_jours Then
With Cells(jour + 2, colonne).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
'----------------------------------------------------------------------------------------------------
'DROITE
With Cells(jour + 2, colonne + 1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With Cells(jour + 2, colonne + 1).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With Cells(jour + 2, colonne + 1).Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
End With
If Weekday(date_du_jour) = 1 Then ' si dimanche
With Cells(jour + 2, colonne + 1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
Else
With Cells(jour + 2, colonne + 1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
End If
If jour = nb_jours Then
With Cells(jour + 2, colonne + 1).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
If mois = 12 Or jour > 28 Then
With Cells(jour + 2, colonne + 1).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
'-----------------------------------------------------------------------------------------------------
Next
Next
'ci-dessous, réintégration des données
'**************************
'i = 1
For i = 1 To UBound(tb)
Cmois = Split(tb(i), "|")(0): Cjour = Split(tb(i), "|")(1): Cvaleur = Split(tb(i), "|")(2)
Ccouleur = Split(tb(i), "|")(3)
For Each cel In Plg
If WorksheetFunction.IsEven(cel.Column) = False Then
If Month(CDate(cel)) = Val(Cmois) And Day(CDate(cel)) = Val(Cjour) Then
cel(1, 2) = Cvaleur: cel(1, 2).Interior.Color = Ccouleur
'i = i + 1
End If
End If
Next cel
Next i
'***************************************
End Sub |
