For Each col In rng.Columns
With wks
If Not Weekday(.Cells(6, col.column).Value, vbMonday) > 5 And IsError(IsHoliday(.Cells(6, col.column).Value)) And IsError(Is2Holiday(.Cells(6, col.column).Value)) And IsError(Is3Holiday(.Cells(6, col.column).Value)) = True Then
If col.Cells.Count = Application.WorksheetFunction.CountBlank(col) Then
MSGBOX "Volle Verfügbarkeit am " & .Cells(5, col.column) & ", den " & .Cells(6, col.column)
Exit Sub
End If
End If
End With
Next
MSGBOX "Es gibt keine komplett leere Spalte in der Ressourcenplanung"
End Sub
Function IsHoliday(d As Date) As Variant
Dim v As Variant
v = Application.Match(CDbl(d), ThisWorkbook.Worksheets("Public Holidays").Columns(1), 0)
IsHoliday = v
End Function
Function Is2Holiday(d As Date) As Variant
Dim v As Variant
v = Application.Match(CDbl(d), ThisWorkbook.Worksheets("Public Holidays").Columns(3), 0)
Is2Holiday = v
End Function
Function Is3Holiday(d As Date) As Variant
Dim v As Variant
v = Application.Match(CDbl(d), ThisWorkbook.Worksheets("Public Holidays").Columns(5), 0)
Is3Holiday = v
End Function