Formula to Insert User Name on Change for Each Row

tsroque

Board Regular
Joined
Jan 19, 2007
Messages
123
I saw a code for this, but it's document specific. If I have to do this in a VBA code, I'm fine with that.

I need to automatically capture the date/time and system user name if anything is a row is changed. So if I change anything in Row 2, the date/time is displayed in A2 and User Name is displayed in B2. If I change anything in Row 3, the date/time is displayed in A3 and User Name is displayed in B3, etc...

I would also like the words "UPDATED" in column C if a change happens in that Row and for the changed cells to be highlighted in YELLOW until the words "APPROVED" appear in that cell (will be from a validation list user can select from).

For the color, user can manually overwrite it just in case they put focus on it and then decide not to change anything.

Thank you!!!
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

Try the following code, which should be placed in the code module corresponding to that particular worksheet:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim Rng As Range
    
    Application.EnableEvents = False
    
    For Each Rng In Target.Cells
        With Rng
            If .Column = 3 And UCase(.Value) = "APPROVED" Then
                .EntireRow.Interior.Pattern = xlNone
            ElseIf .Column > 3 Then
                .Interior.Color = RGB(255, 255, 0)
                Me.Cells(.Row, 1).Value = Now
                Me.Cells(.Row, 2).Value = Environ("UserName")
                Me.Cells(.Row, 3).Value = "UPDATED"
            End If
        End With
    Next Rng
    
    Application.EnableEvents = True

End Sub
 
Last edited:

tsroque

Board Regular
Joined
Jan 19, 2007
Messages
123
How would I change the code to only remove the formatting for a set number of columns? I have formatting in the ADMIN USE ONLY columns of A-E so I need the Interior formatting to clear for 6-13.

Thank you!!!
 

tsroque

Board Regular
Joined
Jan 19, 2007
Messages
123
This may not be pretty but here's what I did...

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Application.EnableEvents = False
    For Each Rng In Target.Cells
        With Rng
            If .Column = 4 = True Then
                Me.Cells(.Row, 6).Interior.Color = RGB(255, 255, 255)
                Me.Cells(.Row, 7).Interior.Color = RGB(255, 255, 255)
                Me.Cells(.Row, 8).Interior.Color = RGB(255, 255, 255)
                Me.Cells(.Row, 9).Interior.Color = RGB(255, 255, 255)
                Me.Cells(.Row, 10).Interior.Color = RGB(255, 255, 255)
                Me.Cells(.Row, 11).Interior.Color = RGB(255, 255, 255)
                Me.Cells(.Row, 12).Interior.Color = RGB(255, 255, 255)
                Me.Cells(.Row, 13).Interior.Color = RGB(255, 255, 255)
            ElseIf .Column > 5 Then
                .Interior.Color = RGB(255, 255, 0)
                Me.Cells(.Row, 1).Value = Now
                Me.Cells(.Row, 2).Value = Environ("UserName")
                Me.Cells(.Row, 3).Value = "UPDATED"
            End If
        End With
    Next Rng
    Application.EnableEvents = True
End Sub

It sets the Interior color for Columns 6-13 to white if Column 4 is True. NOW...how can I automatically clear columns 4 and 5 if the user changes something again?

For example, if they change cell F3...A3-C3 updates with the date, UserName and "UPDATED", and F3 interior is YELLOW. Then I approve it in D3 and E3 and F3 thru M3 interior is white, but if the user goes in after I approve it, it does not change again. Is there a way to factor future use in?

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,984
Messages
5,526,062
Members
409,684
Latest member
Nazmul00

This Week's Hot Topics

Top