Recording time for dynamically changing cell values

Is it possible to record only time in B1 when a cell A1 in sheet1 changes dynamically not manually

Like that around I need 5 cells say A1,A2,A3,A4,A5 record time when they get updated automatically in B1,B2,B3,B4,B5 respectively

so like this whenever changes occur in these cells that time should be recorded automatically every time.

Hope your answer helps me

Thank you

Hello
Using the worksheet change event for A1:A5:

Please Login or Register  to view this content.

DBY

Sir,this code was working only when "A" column cells are changed manually,not when it was formulae or dynamically changing value,

Sir is it possible to get only time not date......

TQ

Hello
Sorry, wasn't thinking correctly, I thought you meant the Time to be recorded automatically. It's a little more difficult to do but after a bit of research I found the following code:

Please Login or Register  to view this content.

If A1:A5 have formulas and their values change then the time stamp is placed in the adjacent column.

Hope this is what you're looking for.
DBY

Sir ,
I pasted this recent code but nothign was happening sir,
no time is recorded.can you guide me how to use this code perfectly

Are you placing the code in the correct place? It needs to go into the Worksheet Change Event. Attached is a screenshot.


VBA Editor Screenshot.jpg

Sir it is working ,i felt very happy....
But unfortunately one thing happened.
Say A1 = B1 + C1 ( B1,C1 are the cells of same sheet ) this is working
but here my formulae for A1 = B1+ C1 ( where these cells are from other sheet /sometimes from other workbook also)
help me over this
TQ sir

try this

Please Login or Register  to view this content.

try this formula in B1 and copy down

Please Login or Register  to view this content.

but what is happening ,what ever there is change in sheet or workbook time is changing,time to be recorded only for range (a1:a5)

can you post an example of you spreadsheet

click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

@ mahe1188, I hadn't realized that using 'Dependants' only works on the active sheet, so unfortunately this method cannot be used as required. I don't have any more suggestions at the moment sorry.

@ Toonies
I too initially suggested a Change Event code but that of course doesn't fire with formula changes which is what the OP wants. The Calculate Event cannot Target specific cells and the NOW() worksheet function will recalculate with every sheet calculation so is not a static time stamp.

Ayyoo......


Anyway I am happy for your replies and active participation in solving problems.

Thank you ....

Let see some where I may get the solution for this

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rDependents As Range
On Error Resume Next

Set rDependents = Target.Dependents

If Err.Number > 0 Then
Exit Sub
Else
If Not Intersect(rDependents, Range("A1:A5")) Is Nothing Then
rDependents.Offset(0, 1) = Time
End If
End If

End Sub


for the above code
when cell is selected ,time is recording but time need to be recorded when cell values are changing .....can anyone help me over this