Sub KalenderAusfuellen2()
Const cZ_ERSTEDATUMSZEILE = 2
Const cCOLORINDEXWOCHENENDE = 6
Const cCOLORINDEX4TEFREITAG = 12
Dim ws As Worksheet
Dim lJahr As Long, dDate As Date, lAnzFreitage As Long, sMonat As String
Dim Monate As Variant, m As Long, t As Long, b As Long
Dim lWochentag As Long, sWochentag As String, lFarbe As Long
Dim tLetzterFreitag As Long
Monate = Array(Januar, Februar, März, April, Mai, Juni, Juli, _
August, September, Oktober, November, Dezember)
->neues Blatt in activer Mappe anlegen
Set ws = ActiveWorkbook.Worksheets.Add
->aktuelles Jahr bestimmen
lJahr = Year(Now)
->Datum auf den 31.12 des Vorjahres initialisieren
dDate = 1 & . & 1 & . & lJahr
dDate = dDate - 1
->Freitagszähler
lAnzFreitage = 0
->alle Monate
For m = 1 To 12
->Monatsüberschrift
With ws.Cells(cZ_ERSTEDATUMSZEILE - 1, m)
.Value = Monate(m - 1)
.Font.Bold = True
For b = 7 To 10
With .Borders(b)
.LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
End With
Next
End With
->alle Tage, wenn vorhanden
For t = 1 To 31
dDate = dDate + 1
If Month(dDate) = m Then
lWochentag = Weekday(dDate)
Select Case lWochentag
Case 1: sWochentag = So: lFarbe = cCOLORINDEXWOCHENENDE
Case 2: sWochentag = Mo: lFarbe = xlColorIndexNone
Case 3: sWochentag = Di: lFarbe = xlColorIndexNone
Case 4: sWochentag = Mi: lFarbe = xlColorIndexNone
Case 5: sWochentag = Do: lFarbe = xlColorIndexNone
Case 6: sWochentag = Fr: lFarbe = xlColorIndexNone
tLetzterFreitag = t->merken
Case 7: sWochentag = Sa: lFarbe = cCOLORINDEXWOCHENENDE
End Select
With ws.Cells(t + cZ_ERSTEDATUMSZEILE - 1, m)
.Value = Format(t, #0) & & sWochentag
.Interior.ColorIndex = lFarbe
For b = 7 To 10
With .Borders(b)
.LineStyle = xlContinuous: .Weight = xlThin: .ColorIndex = xlAutomatic
End With
Next
End With
Else
dDate = dDate - 1: Exit For
End If
Next
->letzten gemerkten Freitag im Monat farbig setzen
ws.Cells(tLetzterFreitag + cZ_ERSTEDATUMSZEILE - 1, _
m).Interior.ColorIndex = cCOLORINDEX4TEFREITAG
Next
With ws.PageSetup
->Überschrift, fett, Schriftgröße 14
.CenterHeader = &B&14Jahreskalender & lJahr
->A4
.PaperSize = xlPaperA4
->Querformat
.Orientation = xlLandscape
->eine Seite breit
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 200
End With
End Sub