Cell value change event

[yugant]

hello I have alteast 1000 rows and 1 column in excel sheet and each
cell in the excel sheet has a formula. I need to track the change in
each cell. If i were using excel_change event it would have been easy
for me to track the address of the changing cell. Since i am using
formulas in all cell i can trap the change event only by
sheet_calculate event ,but the drop back for this event is i can't trap
the address of the changing cell. Is there any way i can trap the
address of the changing cell value which has formula defined in it????

 

[Guest]

try this....it uses sheet2!A1:A1000 to save the values from sheet1!A1:A1000
when sheet1 calculates, teh cvalues are checked, the the latest values
copied back to sheet2

Put this code on Sheet1's code page:

Option Explicit
Private Sub Worksheet_Calculate()
CheckValues
End Sub
Sub setValues()
Worksheets("sheet2").Range("A1:A1000").Value = _
Worksheets("sheet1").Range("A1:A1000").Value
End Sub

Sub CheckValues()
Application.EnableEvents = False

With Worksheets("sheet1").Range("B1:B1000")
.Formula = "=IF(A1 = Sheet2!A1,"" "",""CHANGED"" )"
.Value = .Value
End With
setValues
Application.EnableEvents = True
End Sub


if you want to see what the prev value was, change
.Formula = "=IF(A1 = Sheet2!A1,"""",""CHANGED"" )"
to
.Formula = "=IF(A1 = Sheet2!A1,"""", Sheet2!A1)"