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

Petersburg1
Petersburg1 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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

Author

Commented:
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


Commented:
Attached you will find the update version of the file and also the code.

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
        Target.Offset(0, 7).Value = Now()
        Target.Offset(0, 8).Value = Environ("UserName")
    End If

End Sub

Open in new window

Cell-Change.xlsm
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

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

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

jppinto

Author

Commented:
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

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial