Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Automatic Update

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    One of my co-workers is creating a spreadsheet with a list of values in one column and the date when these values changed in another column. Is there a way to code the date column to automatically change to the current date when the value changes? Any help would very much be appreciated.


  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi dlomas,

    In order to answer your question it is necessary to know HOW the values in the first column are changing. Are they changing

    1. by being manually entered/edited?

    2. because they contain a formula that makes them dependent on another cell that changes?

    3. as a result of being set by a VBA macro or event?

    In addition, do you want to capture changes to the cell's formatting? Or just to its contents?

    Damon

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Damon, The cells will be manually edited and it's just the contents of the cell that we want to capture the changes of. Thanks for your help!

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again Dlomas,

    Okay, if you just want to capture manual entries, this is very easy. Here's sample code that will record in column C the date that any value in Column B is manually edited. Be sure to format column C in a date format.

    Install this code in the worksheet's event code module. To do this, right-click on the worksheet tab, select View Code, and paste this code into the VBE code pane that appears, and voila!

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
    'store current date/time in column 3
    Cells(Target.Row, 3) = Now()
    End If
    End Sub

    If you want to further restrict this routine to looking at just on a certain range of rows in column 2, the If test would look like:

    If Target.Column = 2 AND Target.Row > 5 AND Target.Row < 20 Then

    Which would look at only B6:B19. Much more complex ranges can be accommodated using the Intersect function.

    Enjoy and


    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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