+ Reply to Thread
Results 1 to 6 of 6

Automatically add date when cells values change

Hybrid View

  1. #1
    Registered User
    Join Date
    07-27-2013
    Location
    Kansas city
    MS-Off Ver
    Excel 2010
    Posts
    2

    Automatically add date when cells values change

    I am attempting to have the date automatically inserted in to the cell right of the selected cell on 2 separate columns (A and G), I have found a script that works perfect for column "A", but cannot make it work for both "A" and "G". So if I change the value on A2, B2 will add the date, and if I change the value on G4, H4 will add the date, looking for this to work for the entire column

    Here is the code that works well for column A:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Set t = Target
    Set a = Range("a:a")
    If Intersect(t, a) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    t.Offset(0, 1).Value = Date
    Application.EnableEvents = True
    End Sub

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Automatically add date when cells values change

    Try:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,G:G")) Is Nothing Then Exit Sub
    If Target.Column = 1 Then
        Target.Offset(0, 1) = Date
    ElseIf Target.Column = 7 Then
        Target.Offset(0, 1) = Date
    End If
    End Sub

  3. #3
    Registered User
    Join Date
    07-27-2013
    Location
    Kansas city
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Automatically add date when cells values change

    Worked perfect, thankyou

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Automatically add date when cells values change

    Even more shorter version........

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,G:G")) Is Nothing Then Exit Sub
        Target.Offset(0, 1) = Date
        Target.Offset(0, 1) = Date
    End Sub
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 365
    Posts
    8,026

    Re: Automatically add date when cells values change

    My pleasure.

    Even shorter:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,G:G")) Is Nothing Then Exit Sub
        Target.Offset(0, 1) = Date
    End Sub

  6. #6
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Automatically add date when cells values change

    Quote Originally Posted by Mumps1 View Post
    My pleasure.

    Even shorter:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A,G:G")) Is Nothing Then Exit Sub
        Target.Offset(0, 1) = Date
    End Sub
    lol...correct. I missed that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Change values automatically
    By Kribulin in forum Excel General
    Replies: 2
    Last Post: 06-04-2008, 03:06 AM
  2. How can I automatically add up the values of cells by date?
    By maxedison in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-07-2008, 05:40 PM
  3. [SOLVED] All values automatically change to zero
    By Ricky in forum Excel General
    Replies: 1
    Last Post: 07-05-2005, 12:05 PM
  4. Automatically Insert DATE, so that DATE will NOT change
    By Cie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-04-2005, 03:37 PM
  5. Replies: 1
    Last Post: 01-11-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1