I need help with automatic last updated cell

[juden1]

Hey!

I had this VBA code working until i put a formula in in J15 in the "Data" workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("J15")) Is Nothing Then Exit Sub
Range("K18") = Now()
End Sub



My plan is to paste the data from A1 in "hej.xlsm" to J15 in "Data.xlsm" based on current date. And in K18 in "Data.xlsm" i want to see when the value in the J15 cell was last changed or updated. As i mentioned, as soon as a formula changes the cells value it stopped working.

Any ideas?

 

[JoeMo]

When a formula cell updates, VBA does not see that change as a Worksheet_change event so your change event code is not triggered. You could use a Workbook_SheetCalculate event so that each time the sheet calculates and your formula cell updates you put a time stamp in the cell of your choice. Alternatively, you may be able to use a non-formulaic cell that is a precedent cell to the formula cell to trigger the worksheet_change event code.

 

[juden1]

When a formula cell updates, VBA does not see that change as a Worksheet_change event so your change event code is not triggered. You could use a Workbook_SheetCalculate event so that each time the sheet calculates and your formula cell updates you put a time stamp in the cell of your choice. Alternatively, you may be able to use a non-formulaic cell that is a precedent cell to the formula cell to trigger the worksheet_change event code.

Oh right, SheetCalculate. Would you help me how it would look like instead? Im kind of new to VBA and Excel..

 

[juden1]

Anyone got any ideas?
Best regards