asked on

Any change in a range of cells shall trigger date change in one cell and write current user name in another cell

Dear Experts,
hope my request can be done with a formula...if not I'm open for a VBA solution but please keep it good documented as I'm not a VBA dummy only....

If I change a value (text or date or figure) in a cell in the range A1:A10, I want that a formula will show "today()" in A11 and a second formula in A12 shall show me based on that the name of the current computer user.

thank you

jppinto

You can do it with this code. I've attached a sample working file for you to check it.

jppinto

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Range("A1:A10")


    If Not Intersect(Target, rng) Is Nothing Then
        ActiveSheet.Cells(11, 1).Value = Now()
        ActiveSheet.Cells(12, 1).Value = Environ("UserName")
    End If

End Sub

Open in new window

Cell-Change.xlsm

Petersburg1

ASKER

Hi Genius,
yes and not...my fault...
1. Range is not A1:A10 but A1 to F1 but I guess this is easy..can change in the code...
2. But this does cover now only one row and I need to work it all the rows down. Each row is a new set of information and could be changed.

If change in A1:F1 add in G1 = Date and H1 = user
If change in A2:F2 add in G2 = Date and H2 = user
If change in A3:F3 add in G3 = Date and H3 = user
etc.
up to
If change in A300:F300 add in G300 = Date and H300 = user
would be enough.

thanks in advance for your help
Nils

ASKER CERTIFIED SOLUTION

jppinto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Petersburg1

ASKER

Perfect!
thanks so much. Now I can enjoy my weekend even better :-)
regards
Nils

jppinto

Glad to help turn your weekend even better! Have a nive weekend and thanks for the grade! :)

jppinto

Petersburg1

ASKER

Urgs :-(
too early happy!!!

All works fine but only if I perform changes in A1 or B1 or C1! If I perform changes in A2 or B2 etc. the result is moving respectively to the right! I get Now() not at G1 but H1 and the name not in H1 but I1 and so on....
could you fix that please?
thanks
Nils

jppinto

Sorry about that. Here you go!

jppinto

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Range("A1:F300")

    If Not Intersect(Target, rng) Is Nothing Then
    Debug.Print (Target.Column)
        Target.Offset(0, 7 - Target.Column).Value = Now()
        Target.Offset(0, 8 - Target.Column).Value = Environ("UserName")
    End If

End Sub

Open in new window

Cell-Change.xlsm

Petersburg1

ASKER

Hi Genius,
thank you for the quick correction now it works.