Formula to Insert User Name on Change for Each Row

[tsroque]

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!!!

 

[ParamRay]

.
.

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

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

 

[tsroque]

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]

This may not be pretty but here's what I did...

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!