How to stop weekend date input

[Apple08]

Hi everyone

I want to avoid users to input a weekend or holiday date in the cell B10. I have a range of holidays and named as 'Holiday'. If user input a date such as 25/12/2018, then the date will automatically change to a day before the holiday or weekend e.g. 24/12/2018

If this is not possible to do it in the same field, I would consider to let users to input date in cell B11, then B10 will automatically work out the workday before the weekend or holiday input in B11.

Any help would be appreciated.

Regards
Elsa

 

[PCL]

Last run to show a nicer code ... following Mick 's code ...!

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Const HRgName  As String = "Holidays"
Dim WkRg  As Range
Set WkRg = Union([B10], [BF12], [H15], [M22])
Dim F
    If (Intersect(WkRg, Target) Is Nothing) Then Exit Sub
    Application.EnableEvents = False
        Set F = Range(HRgName).Find(Target.Value, LookIn:=xlValues, Lookat:=xlWhole)
        If (Not F Is Nothing) Then
            MsgBox (" This is an holidays ")
            Target = ""
            Application.EnableEvents = True
            Exit Sub
        End If
        If ((Weekday(Target, 2) = 6) Or _
            (Weekday(Target, 2) = 7)) Then
            MsgBox (" This is a weekend ")
            Target = ""
            Application.EnableEvents = True
            Exit Sub
        End If
    Application.EnableEvents = True
End Sub

 

[MickG]

You're welcome

 

[Apple08]

Hi PCL, the message boxes are showing near the 'Holiday' range column, I would like to position them close to B10. Please is there a way to move the message boxes? Many thanks.

 

[PCL]

What about to prepare a custom validation list using a formula, we could expect the alert message close to the cell concerned.
For B10 , select Data > Data Validation > Custom >Setting > Custom
use next formula : =AND((ISERROR(MATCH(B10;Holidays;0)));(WEEKDAY(B10;2)<6))
Prepare the alert message as needed
Change B10 for others cells

 

[Apple08]

Thanks. I pasted in the formula:

=AND((ISERROR(MATCH(B10;Holiday;0)));(WEEKDAY(B10;2)<6)) into the data validation for B10, however it prompts there is a problem with this formula.​

 

[PCL]

Does it show where is the for ?
Change the ; to , in the formula

 

[Apple08]

Does it show where is the for ?
Change the ; to , in the formula

It works now after changing ; to ,

Many thanks for your help.