Results 1 to 7 of 7

Thread: Date Stamping particular cells in spreadsheet

  1. #1

    Date Stamping particular cells in spreadsheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have an Excel 2007 spreadsheet that I share with several other people. I would like to date stamp the last time certain columns are updated (adding new data to a empty cell or changing existing data). Is there a formula I could put in D9 (for example) that would monitor C9 for changes and when C9 changes, D9 would include a date stamp?
    Thanks for future help!!
    Tim

  2. #2
    This will enter todays date in Cell D9 when a change is made to Cell C9

    paste the supplied code into Private Sub Worksheet_Change(ByVal Target As Range) routine.

    also set date format you prefer in cell D9 properties.


    1) Open the VB editor
    1) On the left pane window, double click the sheet where you need your code to run.
    2) Now, at the top of the code window you will see... (General) with a drop down, and (Declarations) with a drop down.
    3) Click the drop down by (General) and select Worksheet.
    4) Clcik the drop down by (Declarations) and select change

    it should automatically create


    Private Sub Worksheet_Change(ByVal Target As Range)


    End Sub

    Code:
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Address = "$C$9" Then
    Application.EnableEvents = False
    Range("D9").Value = Format(Date, "dd/mm/yy")
     
    
    'Turn events back on
    Application.EnableEvents = True
    'Allow run time errors again
    On Error GoTo 0
    End If

  3. #3
    If you want to monitor Column C for changes made in any cell use this code

    Code:
    If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Sheets("sheet1").Range("c:c")) Is Nothing Then
    Application.EnableEvents = False
    Range("D9").Value = Format(Date, "dd/mm/yy")
     
    
    'Turn events back on
    Application.EnableEvents = True
    'Allow run time errors again
    On Error GoTo 0
    End If

  4. #4
    Ok, that worked perfectly! Could I possibly ask for one more enhancement? Is there a way to monitor each cell in Column C and have it put the date of change in the corresponding row in Column D? For example, each time C10 changes, D10 is populated with a date stamp and each time C11 changes, D11 is stamped? That was my original question but I didnt word it correctly. Thank you so much for you help. I love this forum! It never fails me!!

  5. #5
    This should fix you up.

    Code:
      If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Not Intersect(Target, Sheets("sheet1").Range("c:c")) Is Nothing Then
    Application.EnableEvents = False
    Target.Offset(0, 1) = Format(Date, "dd/mm/yy")
    'Turn events back on
    Application.EnableEvents = True
    'Allow run time errors again
    On Error GoTo 0
    End If

  6. #6
    If you need a time stamp as well as date use this line of code instead of current one. I thought this might help you in case a cell gets updated more than once during a given day.

    Code:
    Target.Offset(0, 1) = Format(DateTime.Now, "yyyy-MM-dd hh:mm:ss")

  7. #7
    Absolutely without a doubt PERFECT!! I will say it again. This is the best website on the internet!!
    Thank you so much.
    Tim

Posting Permissions

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