Gentlemen, I´d like to create a log of events. With a form I´ll fill cells on the same row starting in column C and I´d like date and time printed automatically in columns A and B. Sometimes information might start from column D and C would be empty. The code below does that through an offset from column C and C only. Even if I expand the range for Intersect, the date and time will be carried out and not stay fixed in A and B. Does anyone would have a hint for me?
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rCell As Range Dim rChange As Range On Error GoTo ErrHandler Set rChange = Intersect(Target, Range("C:C")) If Not rChange Is Nothing Then Application.EnableEvents = False For Each rCell In rChange If rCell > "" Then With rCell.Offset(0, -2) .Value = Date .NumberFormat = "[$-2C09]ddd, DD/MM/YYYY" .HorizontalAlignment = xlLeft .EntireColumn.AutoFit End With With rCell.Offset(0, -1) .Value = Time .NumberFormat = "hh:mm" .HorizontalAlignment = xlCenter .EntireColumn.AutoFit End With Else rCell.Offset(0, -1).Clear rCell.Offset(0, -2).Clear End If Next End If ExitHandler: Set rCell = Nothing Set rChange = Nothing Application.EnableEvents = True Exit Sub ErrHandler: MsgBox Err.Description Resume ExitHandler End Sub