Ich glaub es wird zeit, das ich endlich urlaub hab. steh momentan voll aufem schlauch.
Ich habe ne liste an Datumsvariablen.
diese möchte ich jeweils mit nem ausgewählten datum abgleichen.
wenn das gewählte datum einem aus der liste entspricht, soll die spalte grau werden...
soweit so gut...
Das Datum auswählen klappt.
Wie verkürze/-einfach ich den abgleich mit den 16 Daten?
Sub Feiertage()' am 2003-04-15 von Marc Voelker aufgezeichnet
Dim x As Single
Dim OS As Date, OM As Date, CH As Date, PS As Date, PM As Date, FL As Date, KF As Date
Dim NJ As Date, AH As Date, TdE As Date, TdA As Date, MH As Date, HDK As Date, HA As Date, HA1 As Date, HA2 As Date
Dim a As Long, b As Long, c As Long
OS = CDate("1 / 1 / 1")
' Ostern berechnen - geht, aber k.a. wie...
If (((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) + ((32 + 2 * ((Year(Now()) \ 100) Mod 4) + 2 * (((Year(Now())) Mod 100) \ 4) - ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) - ((Year(Now())) Mod 100) Mod 4) Mod 7) - 7 * ((((Year(Now())) Mod 19) + 11 * ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) + 22 * ((32 + 2 * ((Year(Now()) \ 100) Mod 4) + 2 * (((Year(Now())) Mod 100) \ 4) - ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \
100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) - ((Year(Now())) Mod 100) Mod 4) Mod 7)) \ 451) + 22) - 31 < 1 Then
x = ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) + ((32 + 2 * ((Year(Now()) \ 100) Mod 4) + 2 * (((Year(Now())) Mod 100) \ 4) - ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) - ((Year(Now())) Mod 100) Mod 4) Mod 7) - 7 * ((((Year(Now())) Mod 19) + 11 * ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) + 22 * ((32 + 2 * ((Year(Now()) \ 100) Mod 4) + 2 * (((Year(Now())) Mod 100) \ 4) - ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \
100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) - ((Year(Now())) Mod 100) Mod 4) Mod 7)) \ 451) + 22
Else
x = (((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) + ((32 + 2 * ((Year(Now()) \ 100) Mod 4) + 2 * (((Year(Now())) Mod 100) \ 4) - ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) - ((Year(Now())) Mod 100) Mod 4) Mod 7) - 7 * ((((Year(Now())) Mod 19) + 11 * ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) + 22 * ((32 + 2 * ((Year(Now()) \ 100) Mod 4) + 2 * (((Year(Now())) Mod 100) \ 4) - ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \
100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) - ((Year(Now())) Mod 100) Mod 4) Mod 7)) \ 451) + 22) - 31
End If
If (((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) + ((32 + 2 * ((Year(Now()) \ 100) Mod 4) + 2 * (((Year(Now())) Mod 100) \ 4) - ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) - ((Year(Now())) Mod 100) Mod 4) Mod 7) - 7 * ((((Year(Now())) Mod 19) + 11 * ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \ 100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) + 22 * ((32 + 2 * ((Year(Now()) \ 100) Mod 4) + 2 * (((Year(Now())) Mod 100) \ 4) - ((19 * ((Year(Now())) Mod 19) + (Year(Now()) \ 100) - ((Year(Now()) \ 100) \ 4) - ((Year(Now()) \
100) - (((Year(Now()) \ 100) + 8) \ 25) + 1) \ 3 + 15) Mod 30) - ((Year(Now())) Mod 100) Mod 4) Mod 7)) \ 451) + 22) > 31 Then
' MsgBox DateSerial(Year(Date), 4, x)
OS = DateSerial(Year(Date), 4, x)
Else
' MsgBox DateSerial(Year(Date), 3, x)
OS = DateSerial(Year(Date), 3, x)End If
'Osternabhängige Feiertage
KF = OS - 2
OM = OS + 1
CH = OS + 39
PS = OS + 49
PM = OS + 50
FL = OS + 60
'fixe Feiertage
NJ = DateSerial(Year(Date), 1, 1)
AH = DateSerial(Year(Date), 11, 1)
TdE = DateSerial(Year(Date), 10, 3)
TdA = DateSerial(Year(Date), 5, 1)
MH = DateSerial(Year(Date), 8, 15)
HDK = DateSerial(Year(Date), 1, 6)
HA = DateSerial(Year(Date), 12, 24)
HA1 = DateSerial(Year(Date), 12, 25)
HA2 = DateSerial(Year(Date), 12, 26)
Sheets("Zeitplan").Select'Auslesen der letzten Spalte
c = Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).column
c = c - 1
For b = 8 To c
If Cells(1, b) = OM Then
'markiert die Zelle grau
Columns(b).Interior.ColorIndex = 15
End If
Next
End Sub
Code eingefügt mit Syntaxhighlighter 2.0
Gruß+Dank
der, der aufem schlauch steht