Sub payday()
'MAY
Dim MayArray As Variant
MayArray = Worksheets("Calendar").Range("MAY")
For i = LBound(MayArray) To UBound(MayArray)
If cell.Value = 15 Then
If ColorIndexOfCF(i, False) = -4142 Then 'cell already has a color in it (holiday)
If i - 1 = MayArray(, 7) Then '(saturday)
payday = i - 2
Cells.Interior.ColorIndex = 4 'color cell green
If i - 1 = MayArray(, 1) Then '(sunday)
payday = i - 3
Cells.Interior.ColorIndex = 4 'color cell green
Else '(mon-fri)
payday = i - 1
Cells.Interior.ColorIndex = 4 'color cell green
End If
ElseIf i - 1 = MayArray(, 7) Then '(saturday)
payday = i - 2
Cells.Interior.ColorIndex = 4 'color cell green
ElseIf i - 1 = MayArray(, 1) Then '(sunday)
payday = i - 3
Cells.Interior.ColorIndex = 4 'color cell green
Else '(the day must be on a non-holiday weekday so color normally)
Cells.Interior.ColorIndex = 4 'color cell green
End If
ElseIf cell.Value = 30 Then
If ColorIndexOfCF(i, False) = -4142 Then 'cell already has a color in it (holiday)
If i - 1 = MayArray(, 7) Then '(saturday)
payday = i - 2
Cells.Interior.ColorIndex = 4 'color cell green
If i - 1 = MayArray(, 1) Then '(sunday)
payday = i - 3
Cells.Interior.ColorIndex = 4 'color cell green
Else '(mon-fri)
payday = i - 1
Cells.Interior.ColorIndex = 4 'color cell green
End If
ElseIf i - 1 = MayArray(, 7) Then '(saturday)
payday = i - 2
Cells.Interior.ColorIndex = 4 'color cell green
ElseIf i - 1 = MayArray(, 1) Then '(sunday)
payday = i - 3
Cells.Interior.ColorIndex = 4 'color cell green
Else '(the day must be on a non-holiday weekday so color normally)
Cells.Interior.ColorIndex = 4 'color cell green
End If
End If
Next i
End Sub