Highlight Columns if the date falls to weekend or holidays

[Vincent88]

Hi All,
Please help to modify the code to make the column appearance change - the font to be red if the column date falls to holiday ; interior change to some color if it is weekend; and font to be black if it is weekdays.

Public Function IsHolWeekend(InputDate As Date) As Boolean

    Dim vLastRow As Long
    Dim vR1 As Range

    With ThisWorkbook.Worksheets("Data")
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        For Each vR1 In .Range("A2:A" & vLastRow)
            If Day(InputDate) = Day(vR1) And _
               Month(InputDate) = Month(vR1) And _
               Year(InputDate) = Year(vR1) Then
               IsHolWeekend = 1
              
            ElseIf Weekday(InputDate) = 1 Or Weekday(InputDate) = 7 Then
               IsHolWeekend = 2
              
            Else
                IsHolWeekend = 0
                'Exit Function
            End If
        Next vR1
     End With
    
End Function

Sub HolidayandWeekend19()

    Dim vRng As Range
    Dim vLastRow As Long
    Dim vRngCol As Range
    
    With ThisWorkbook.ActiveSheet
        vLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        Set vRng = Range("C1", Range("AL" & vLastRow))
        For Each vRngCol In vRng.Columns
            If IsHolWeekend(vRngCol.Cells(1)) = 1 Then
                With Columns(vRngCol.Column)
                    .Font.Color = vbRed
                End With
            ElseIf IsHolWeekend(vRngCol.Cells(1)) = 2 Then
                With Columns(vRngCol.Column)
                    .Interior.Color = RGB(255, 245, 230)
                End With
            Else
               With Columns(vRngCol.Column)
                    .Font.Color = vbBlack
                End With
                
            End If
          
        Next vRngCol
    End With
    
 End Sub

 

[johnnyL]

You mean it works only one coincidence ?

I mean you have only checked for Saturday And Holiday

If you want to check for Saturday or Sunday And a Holiday you may want to change:

            If Weekday(vRngCol.Cells(1)) = 7 And IsHolWeekend(vRngCol.Cells(1)) = True Then  'If holiday coincides with weekend

to:

            If Weekday(vRngCol.Cells(1)) = 7 Or Weekday(vRngCol.Cells(1)) = 1 And IsHolWeekend(vRngCol.Cells(1)) = True Then  'If holiday coincides with weekend

 

[Vincent88]

Hi johnnyL,
Thank you for your reminder. In fact, there is no statutory holiday on Sunday.

 

[johnnyL]

A holiday can fall on a Sunday, but usually 'Observed' on the next day. You didn't originally specify statutory holiday.