Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

Record date and time in when data changed in a cell

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
rduval
Asked:
rduval
  • 6
  • 6
  • 2
  • +1
1 Solution
 
StephenJRCommented:
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
 
sungenwangCommented:
You can use this VBA solution:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=863

sew


0
 
JefKveCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rduvalAuthor Commented:
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
 
StephenJRCommented:
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
 
JefKveCommented:
No you wont as its looking anywhere in the sheet for the change, not just at a single row/cell....
0
 
rduvalAuthor Commented:
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
 
StephenJRCommented:
Why not post a sample workbook and we can give specific advice.
0
 
rduvalAuthor Commented:
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
 
rduvalAuthor Commented:
I guess I need to know how to target cells W10:Z1000 for instance
0
 
StephenJRCommented:
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
 
StephenJRCommented:
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
 
rduvalAuthor Commented:
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
 
StephenJRCommented:
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
 
rduvalAuthor Commented:
Bingo! Thanks all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now