If Weekday() = vbSaturday Or Weekday() = vbSunday Then

[Excelnoub]

Good day,
I am looking for help on proceeding to the following:

I have codes that works as ActiveCell.Row therefore if my ActiveCell.Row "I" is double I will have a calendar (Monthview) generate and the user will select a date. If the user selects a weekend date then my calendar will have a msgbox saying they cannot choose a Weekend date:

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)  
    If Weekday(MonthView1) = vbSaturday Or Weekday(MonthView1) = vbSunday Then
        MsgBox "Date cannot fall on a weekend, please try again"
    Else
        ActiveCell.Value = Format(MonthView1.Value, "YYYY/MM/DD")
    End If 
    Unload Me
End Sub

In my sheet, when everything is done properly, I have the following code that will be added:

    'This will change the color of column I,J and K when all 3 fields are populated
    If Not Intersect(Target, Range("I5:K20")) Is Nothing Then
        Application.EnableEvents = False
            If IsDate(Cells(ActiveCell.row, 9)) And IsDate(Cells(ActiveCell.row, 10)) And IsDate(Cells(ActiveCell.row, 11)) Then
                Cells(ActiveCell.row, "A").Font.Color = vbRed
            Else
            'If nothing in Cell I to K then change it back to color black
                Cells(ActiveCell.row, "A").Font.Color = vbBlack
            End If
        Application.EnableEvents = True
    End If

I need that If my ActiveCell.row, "K" that when a user selects a date in I and that K's is on the weekend, then to populate a msgbox to say the same thing.

Problem is the following: At the same time that my I is triggered, my Column J and K will have the following:

   'This function will add the formula: =IF($I5="","",$I5+40) to the active row on column K
   If Not Intersect(Target, Range("I5:I20")) Is Nothing Then
      Application.EnableEvents = False
      For Each rcell In Intersect(Target, Range("I5:I20")).Cells
         If Len(rcell.Value) > 0 Then
            Cells(ActiveCell.row, "K").FormulaR1C1 = "=IF(RC9="""","""",RC9+40)"
            Cells(ActiveCell.row, "J").Value = Cells(ActiveCell.row, "K").Value
         Else
         'If nothing is in the active cell I, then it will remove anything that is in the Active Row, Column K and J
            Cells(Target.row, "K").ClearContents
            Cells(Target.row, "J").ClearContents
         End If
      Next rcell
      Application.EnableEvents = True
   End If

Is there a way to make it look, after the fact, to generate an error message if the date, in my Activecell.row "K", is on a weekend and if so Generate the error msg then select my ActiveCell.Row "J"?

I don't need a loop, just an error message then select my actvrow J.

[Excelnoub]

I have modify the following code to have a value in Column K:

   
'This function will add the formula: =IF($I5="","",$I5+40) to the active row on column K
   If Not Intersect(Target, Range("I5:I20")) Is Nothing Then
      Application.EnableEvents = False
      For Each rcell In Intersect(Target, Range("I5:I20")).Cells
         If Len(rcell.Value) > 0 Then
            Cells(ActiveCell.row, "K").FormulaR1C1 = "=IF(RC9="""","""",RC9+40)"
            Cells(ActiveCell.row, "J").Value = Cells(ActiveCell.row, "K").Value
            Cells(ActiveCell.row, "K").Value = Cells(ActiveCell.row, "J").Value
         Else
         'If nothing is in the active cell I, then it will remove anything that is in the Active Row, Column K and J
            Cells(Target.row, "K").ClearContents
            Cells(Target.row, "J").ClearContents
         End If
      Next rcell
      Application.EnableEvents = True
   End If

[Excelnoub]

Bump ...


...

[Excelnoub]

Found the solution with help from Chandoo's Luke M:

Function IsWeekday(d As Date) As Boolean
IsWeekday = (WorksheetFunction.Weekday(d, 2) < 6)
End Function

    If Not Intersect(Target, Range("I5:I20")) Is Nothing Then
    Application.EnableEvents = False
        For Each rcell In Intersect(Target, Range("I5:I20")).Cells
            If Len(rcell.Value) > 0 Then
                Cells(ActiveCell.row, "K").FormulaR1C1 = "=IF(RC9="""","""",RC9+40)"
                Cells(ActiveCell.row, "J").Value = Cells(ActiveCell.row, "K").Value
                Cells(ActiveCell.row, "K").Value = Cells(ActiveCell.row, "J").Value
                    If IsWeekday(Cells(Target.row, "K").Value) Then
                    Else
                        MsgBox "Actual Date cannot fall on a weekend, please try again"
                        Cells(ActiveCell.row, "J").Select
                    End If
            Else
            'If nothing is in the active cell I, then it will remove anything that is in the Active Row, Column K and J
                Cells(Target.row, "K").ClearContents
                Cells(Target.row, "J").ClearContents
            End If
      Next rcell
      Application.EnableEvents = True
   End If