• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Msg according to typed date Saturday, Sunday or list of holidays

S P P

Member
Good afternoon!
Msg according to typed date Saturday, Sunday or list of holidays
 

Attachments

  • SPP Msg according to typed date Saturday, Sunday or list of holidays.xlsm
    16.2 KB · Views: 7
Marc L
I know how to do Conditional Formatting by changing the color. Updating date in another column I know.
I'm wanting to use a single column. If the date typed is a holiday, the message will show the additional days to be modified.
As for validation, I have no idea how to do it.
 
try:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Z = Evaluate("WORKDAY.INTL(" & Target.Address & "-1,1,1,Tabela2[Holidays])-" & Target.Address)
If Not IsError(Z) Then If Z <> 0 Then MsgBox "Add " & Z & " days"
End Sub
You could add code to restrict its activity to certain ranges.
 
p45cal

Everything you needed. This way I eliminate a column from the table to identify Saturday, Sunday and holiday.

Thanks

if possible

There is a way to add to the code to return the typed cell.
 
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Tabela1[Date]")) Is Nothing Then    'only date column of Tabela1
  If Target.Cells.Count = 1 Then    'only one cell changed
    Z = Evaluate("WORKDAY.INTL(" & Target.Address & "-1,1,1,Tabela2[Holidays])-" & Target.Address)
    If Not IsError(Z) Then
      If Z <> 0 Then
        Target.Select
        MsgBox "Add " & Z & " days to the selected cell (" & Target.Address(0, 0) & ")"
      End If
    End If
  End If
End If
End Sub
 
P45cal

I'm trying to insert a line with this code, but I'm getting an error in If Z = 0 Then

'Code
>>> use code - tags <<<
Code:
With ActiveSheet
       .ListObjects(1).ListRows.Add AlwaysInsert:=True
       .ListObjects(1).Range.Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Offset(1).Activate
End With
 
Last edited by a moderator:
He had modified his VBA code to add Target.Offset(0, 2).Select for this reason it was giving error.
I used it like this and it worked.

>>> as You've noted already <<<
>>> use code - tags <<<

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Tabela1[Date]")) Is Nothing Then    'only date column of Tabela1
  If Target.Cells.Count = 1 Then    'only one cell changed
    Z = Evaluate("WORKDAY.INTL(" & Target.Address & "-1,1,1,Tabela2[Holidays])-" & Target.Address)
    If Not IsError(Z) Then
      If Z <> 0 Then
        Target.Select
        MsgBox "Add " & Z & " days to the selected cell (" & Target.Address(0, 0) & ")"
Else
         Target.Offset(0, 2).Select
      End If
    End If
  End If
End If
End Sub
 
Last edited by a moderator:
Back
Top