Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Dim sDateCol As String Dim sTimeCol As String 'if the cell(s) changed was not in col A or Col F then nothing to do If Intersect(Target, Union(Range("A2:A" & Rows.Count), Range("F2:F" & Rows.Count))) Is Nothing Then GoTo End_Sub ' disable event Application.EnableEvents = False On Error GoTo Error_Handler ' for every modified cell For Each Cell In Target ' if the modified cell is not in col 1 (A) or at col 6 (F) then go to next changed cell If ((Cell.Column <> 1) And (Cell.Column <> 6)) Then GoTo Next_Cell ' if changed cell is at col 1 (col A) If (Cell.Column = 1) Then sDateCol = "B" sTimeCol = "F" Else ' default position that changed cell is at col 6 (col F) sDateCol = "G" sTimeCol = "H" End If ' same row but date col With Cells(Cell.Row, sDateCol) .Value = Date .EntireColumn.AutoFit End With ' same row but time col With Cells(Cell.Row, sTimeCol) .Value = Time .EntireColumn.AutoFit End With Next_Cell: Next Cell End_Sub: Application.EnableEvents = True Exit Sub Error_Handler: MsgBox Err.Description GoTo End_Sub End Sub
Can you please show me, if I am using date in C2 and frequency in D2(in months variable) and like to update in E2
If Not Intersect(Target:range.... the first one
It complains at Intersect