Highlight Columns if the date falls to weekend or holidays

Vincent88

Active Member
Joined
Mar 5, 2021
Messages
382
Office Version
  1. 2019
Platform
  1. Windows
  2. Mobile
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.

VBA Code:
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
 
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:

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

to:

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Hi johnnyL,
Thank you for your reminder. In fact, there is no statutory holiday on Sunday.
 
Upvote 0
A holiday can fall on a Sunday, but usually 'Observed' on the next day. You didn't originally specify statutory holiday.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top