Skipping Days Off

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim myDate As Date
' Check which column change has been made to
' If not within required column then exit sub
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
' If more than one cell is changed at any one time then exit sub
If Target.Count > 1 Then Exit Sub
' Check if data entered into target cell is a date
If IsDate(Target.Value) Then
' Populate myDate variable with date
    myDate = Target.Value
 
' Check if the date is a weekend or bank holiday and
' move forward a day if so until a non-bank holiday weekday
 
    Do While check_WE(myDate) = True Or check_BH(myDate) = True
 
    myDate = myDate + 1
7
    Loop
' Populate required cell with date held in myDate variable
    Target.Offset(0, 1).Value = myDate
End If
End Sub

Public Function check_BH(check_date As Date) As Boolean
 
Dim c As Range
' Check if date passed is in the named range BH_Dates and return
' true or false accordingly
With Range("BH_Dates")
 
    Set c = .Find(check_date, LookIn:=xlValues)
 
    If c Is Nothing Then
 
        check_BH = False
 
    Else
 
        check_BH = True
 
    End If
 
End With
 
End Function
 
 
Public Function check_WE(check_date As Date) As Boolean
' Check if date passed is either Sat or Sun and return
' true or false accordingly
 
If Weekday(check_date, vbMonday) > 5 Then
 
    check_WE = True
 
Else
 
    check_WE = False
 
End If
 
End Function