VBA code to add date/time and username if any cell in same row is changed for rows 2 down.

[MC1903]

Hi,

I am looking to do something similar to the solution to this post - Show last date a cell was modified

I am trying to capture the date/time and username details for changes to any cell in that row (from row 2 downwards), but I need to ignore changes in row 1; as it has my headers/sort.

I want the date/time in col A and the username in col B of each row. Any changes from col C to the right (prob to IZ) for that row should update col A & B for that row.

Carbonite PowerShell - Test.xlsm
	A	B	C	D	E
1	Row Last Changed (Date/Time)	Row Last Changed By User	Data1	Data2	Data3
2	20/01/2022 09:59	mc1903	12	A	Lemons
3	19/01/2022 10:05	mc1903	14	B	Oranges
4	20/01/2022 10:00	mc1903	18	C	Apples
5	20/01/2022 09:59	mc1903	15	D	Oranges
6	19/01/2022 10:05	mc1903	15	E	Apples
7	19/01/2022 10:05	mc1903	19	F	Lemons
8	19/01/2022 10:05	mc1903	19	G	Lemons
9	20/01/2022 09:59	mc1903	21	H	Oranges
10	19/01/2022 10:05	mc1903	12	I	Apples
11	20/01/2022 09:59	mc1903	22	J	Lemons
12	19/01/2022 10:05	mc1903	22	K	Lemons
Sheet1

This is what I have so far, could someone please educate me on what to modify in order to accommodate my needs?

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then     ' Value in Column C changed
        Range("A" & Target.Row).Value = Format(Now, "dd/mm/yyyy hh:MM")                                 ' Update Column A cell value with date/time
        Range("B" & Target.Row).Value = Application.Username                                            ' Update Column B cell value with username
    End If
End Sub

Any help or pointers will be greatly appreciated.

Cheers
M

 

[JW00]

Hi, try changing

If Not Intersect(Target, Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then

to

If Not Intersect(Target, Range("C2:IV" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then

 

[MC1903]

Hi, try changing

If Not Intersect(Target, Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then

to

If Not Intersect(Target, Range("C2:IV" & Range("C" & Rows.Count).End(xlUp).Row)) Is Nothing Then

Cheers @*JW00.

Nice and simple. Really appreciated; I was testing without formatting my cells as a table and it was updating row 1. ?

M

 

[JW00]

Thanks, no worries just glad it's working for you.