Hello there,

I am trying to speed up input of attendance sheet. Upon entry of both start and end hour. (which are in respectively column 3 and 6) I want the standard lunch begin and end hour to appear in column 4 and 5.

So far It doesn't work as supposed to be:
With code below I tried but no success
.. can't seem to find the solution but I think it should be simple.
I am wondering if the syntax is correct .. in any case it doesn't fire the code when columns other than 3 is changed
Any pointers are usefull thankx.

Before I did this behaviour with formula's in column 3 and 4 but the problem is that they are mostly overrided with values if non default lunch
hence the formulas dissapear

I have following code in
VBA in ThisWorkbook:
-------------------------------------------------
Code:
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
Dim lUsed As Long


If ValidSheet(ActiveSheet.Name) Then   'only for certain sheets following should be applied
If Target.Count > 1 Then GoTo exitHandler
On Error Resume Next
Application.EnableEvents = False


Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else

'---> This part works
If Target.Column = 3 Or Target.Column = 4 Or Target.Column = 5 Or Target.Column = 6 Then
' track changes to each input!
Cells(Target.Row, 29).Value = Now
End If

If Target.Column = 3 Or Target.Column = 6 Then
' fill in standard lunch break 30 minutes
' track changes to each input!
  If IsDate(Cells(Target.Row, 3)) And IsDate(Cells(Target.Row, 6)) Then

    If (IsEmpty(Cells(Target.Row, 4))) Then
          MsgBox "Begin/end hours are filled in; standard lunch hours are taken" 
          Cells(Target.Row, 4).Value = "12:00"
    End If
    If (IsEmpty(Cells(Target.Row, 5))) Then
          MsgBox "Begin/end hours are filled in; standard lunch hours are taken" 
          Cells(Target.Row, 5).Value = "12:30"
    End If
  End If
End If


End If

Else  ' Other action for Sheet Settings
  If sh.Name Like "Settings*" Then
    If Not Intersect(Target, Range("B3")) Is Nothing Then Reinitialize
   End If 
End If

exitHandler:
Application.EnableEvents = True
End Sub