Solved

Record date and time in when data changed in a cell

Posted on 2010-09-21
15
251 Views
Last Modified: 2012-05-10
I have a row of cells into which I manually input data. I want to add a cell at the end of the row that shows the date and time when any of the cells was last updated.

They are prices from various suppliers and I want to know how current the pricing is.
0
Comment
Question by:rduval
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
If you had data in A1 to F1 you could try something like this in the sheet module.
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:F1")) Is Nothing Then Exit Sub

Range("G1") = Now()

End Sub

Open in new window

0
 
LVL 14

Expert Comment

by:sungenwang
Comment Utility
You can use this VBA solution:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=863

sew


0
 
LVL 3

Expert Comment

by:JefKve
Comment Utility
Add this to the worksheet where you will be making cahnges as modify as necessary...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
'x x will hold how many columns over we want ot place the change date
x = 2
Target.Offset(0, x) = Now()
End Sub
0
 

Author Comment

by:rduval
Comment Utility
Thanks for the quick comebacks guys but I have about a thousand rows and, unless I'm mistaken, I'd need a thousand pcs of these codes wouldn't I?
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Something like this?
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1:F1000")) Is Nothing Then Exit Sub

Cells(Target.Row, "G") = Now()

End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:JefKve
Comment Utility
No you wont as its looking anywhere in the sheet for the change, not just at a single row/cell....
0
 

Author Comment

by:rduval
Comment Utility
Sorry but I'm not much of an Excel Techie. I can see that a couple of solutions a working here. sungenwangs test files seem to do what I need but I'm a little confused as to how to implement it.

If I'm using:

Sub SetDateRow(Target As Range, Col As String)
    If Target.Cells.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Cells(Target.Row, Col) = Now()
    Application.EnableEvents = True
End Sub

Do I define named ranges?

HOw do I target say, A1:F1000 and have the date written to A10 for the first row, etc.?



0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
Why not post a sample workbook and we can give specific advice.
0
 

Author Comment

by:rduval
Comment Utility
Here's the file from the link that sungenwang sent.

If using the first sheet, I'm not understanding how the vb code is targeting the first 3 cells?

I think the code "SetDateRow Target, "D" is telling the macro to put the date in the D Row is that right?
TestDate.zip
0
 

Author Comment

by:rduval
Comment Utility
I guess I need to know how to target cells W10:Z1000 for instance
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
You could just replace current code with this. Date/time goes in D whenever A-C changed.
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column > 3 Then Exit Sub

Cells(Target.Row, "D") = Now()

End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
My code is effectively the same as the code you had, so you may already have solved the problem! But I don't see how your reference to W10:Z1000 fits with your attachment?
0
 

Author Comment

by:rduval
Comment Utility
I only want to update the date when a value changes within a certain range of cells.

In my case I want to put the date in AI whenever anything changes in AC to AH.

I changed the code to SetDateRow Target, "AI" which puts the date in AI but when any vallue in any column changes then the date changes. I have other data in other columns and I don't want the date updating when that other data changes, only when the data in AC-AH changes.


0
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
Comment Utility
Try this.
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("AC:AH")) Is Nothing Then Exit Sub

Cells(Target.Row, "AI") = Now()

End Sub

Open in new window

0
 

Author Closing Comment

by:rduval
Comment Utility
Bingo! Thanks all
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now