Record time and date

[dukid]

I need to record time and date when value(value is formula result) in specific cell change, I tried some scripts which works fine if value is changed manually but apparently excel doesnt recognizes formula result as change?

[Simon Lloyd]

What you really need is a helper column to be created when the worksheet is activated so, assuming your formula is in column A[VBA]Private Sub Worksheet_Activate()
'change column numbers to suit
Columns(5) = Columns(1).Value
End Sub[/VBA]then in your worksheet calculate[VBA]Private Sub Worksheet_Calculate()
Dim rng As Range, MyCell As Range
Set rng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each MyCell In rng
'checking current value in column A against helper column
If MyCell.Value <> MyCell.Offset(0, 4).Value Then
'add time and date to a predetermined cell, in this case column D
MyCell.Offset(0, 3).Value = Format(Date, "ddd mmm yyyy") & " - " & Format(Time, "hh:mm:ss")
End If
Next MyCell
End Sub[/VBA]

[dukid]

Thank you but this code is returning same results in all cells if value in any of cells in column A changed. What I need is to record time for each cell in column A.
Example: if value change in A1 record time just in B1, if in A2 record time just in B2...

[Chabu]

I think that is impossible.

Worksheet_change does not detect changed results of formulas
Worksheet calculate fires after the calculation is done (so no way to tell if the value changed)
and a user function can only change the cell where it is called from.

Greetings

[mikerickson]

A formula changing will not trigger the Change event.
However, for the most part, if a formula has changed, then a precedent cell has changed.

I imaginge that the insufficiant code mentioned in the OP is a Change event that time stamped (via comment?) Target. If you extended that time stamp to Target.Dependents, it might work for you. But that doesn't cover cross sheet formula, which require a good amount of code.

[mikerickson]

If you are only recording one cell and all of the precedents are on the same sheet, you could put this in the sheet's code module
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim keyRange As Range
Set keyRange = Range("A1")
Set keyRange = Application.Union(keyRange, keyRange.Precedents)
If Not Application.Intersect(Target, keyRange) Is Nothing Then
With Range("A1")
On Error Resume Next
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Format(Now, "dd/mm/yy hh:mm")
On Error GoTo 0
End With
End If
End Sub[/VBA]

[Simon Lloyd]

Thank you but this code is returning same results in all cells if value in any of cells in column A changed. What I need is to record time for each cell in column A.
Example: if value change in A1 record time just in B1, if in A2 record time just in B2...

My code only makes a change opposite the formula result that has changed, it when you activate the worksheet it creates a column of all the values as they are now, then whilst on the worksheet if you change something that causes one of the formula results to change then the time...etc is noted in the offset cell.

Follow my instructions in the attached, also check out where i've put the code!

[Chabu]

I stand corrected, there is always a way....

[dukid]

My code only makes a change opposite the formula result that has changed, it when you activate the worksheet it creates a column of all the values as they are now, then whilst on the worksheet if you change something that causes one of the formula results to change then the time...etc is noted in the offset cell.

Follow my instructions in the attached, also check out where i've put the code!

Your code works fine with formula results but it changes all time results(entire column) to same value if any of formula result in column has changed(this is just for time stamps which are activated so far, others untouched remain blank) unless I go to other worksheet anb back after every time stamp.

[dukid]

If you are only recording one cell and all of the precedents are on the same sheet, you could put this in the sheet's code module
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim keyRange As Range
Set keyRange = Range("A1")
Set keyRange = Application.Union(keyRange, keyRange.Precedents)
If Not Application.Intersect(Target, keyRange) Is Nothing Then
With Range("A1")
On Error Resume Next
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Format(Now, "dd/mm/yy hh:mm")
On Error GoTo 0
End With
End If
End Sub[/VBA]

Thanks but I need to record all cells in specific column, but each separate from others.

[mikerickson]

[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
Dim keyRange As Range
Dim oneCell as Range, aCell As Range
Set keyRange = Range("A:A")
Set keyRange = Application.Union(keyRange, keyRange.Precedents)
If Not Application.Intersect(Target, keyRange) Is Nothing Then
For Each oneCell in Application.Intersect(Target, keyRange)
On Error Resume Next
For Each aCell in oneCell.Dependents
If oneCell.Column = 1 Then
With oneCell
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Format(Now, "dd/mm/yy hh:mm")
End With
End If
Next aCell
Next oneCell
On Error Goto 0
End If
End Sub [/VBA]

[dukid]

Well that could be good temporarily solution.
Thank you very much, to both of you.

[llkojnjhfy]

Thanks, awesome work. This will be very useful!