Hi All,
I have the following code to generate a Week number in column A and a date in column B every time a cell is changed in column C of my spreadsheet. Unfortunately it does not work for multiple entries. For example, If I copy and paste a group of eight entries, it only updates the first row. How can I update the code to allow multiple entries to be updated at once?
Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the edited cell is in column C and not the header row
If Not Intersect(Target, Range("C2:C200")) Is Nothing And Target.Row > 1 Then
' Add today's date to column B for the same row as the edited cell
Cells(Target.Row, "B").Value = Date
' Add the week number to column A for the same row as the edited cell
Cells(Target.Row, "A").Value = WorksheetFunction.WeekNum(Date, vbMonday) - 1
End If
End Sub
Thanks in advance.
I have the following code to generate a Week number in column A and a date in column B every time a cell is changed in column C of my spreadsheet. Unfortunately it does not work for multiple entries. For example, If I copy and paste a group of eight entries, it only updates the first row. How can I update the code to allow multiple entries to be updated at once?
Private Sub Worksheet_Change(ByVal Target As Range)
' Check if the edited cell is in column C and not the header row
If Not Intersect(Target, Range("C2:C200")) Is Nothing And Target.Row > 1 Then
' Add today's date to column B for the same row as the edited cell
Cells(Target.Row, "B").Value = Date
' Add the week number to column A for the same row as the edited cell
Cells(Target.Row, "A").Value = WorksheetFunction.WeekNum(Date, vbMonday) - 1
End If
End Sub
Thanks in advance.