Activate a cell if the result of its formula changes?

[TTom]

Is it possible to set cell active on designated worksheet, within designated range, if the result of its formula changes?
I've been looking at this problem from several angles, still with no success.

Example:
Sheet1, Range("A1:A100") has formulas where --
A1 formula: =Sheet2!A1
A2 formula: =Sheet2!A2
A3 formula: =Sheet3!A1
and so on...

If I change the value in Sheet3, Range("A1") I would want Sheet1, Range("A3") to become active;
this, because the calculated result of Sheet1, Range("A3") was changed when I entered a new value into Sheet3, Range("A1")

After cell is set active I'll need to call a proceedure. After it has run I'll need to set active the cell where I entered the change,
in this case reactivate Sheet3, Range("A1") and end sub.

 

[jindon]

Hi
try
to Standard module

Public oldData() As Variant

to Thisworkbook module

Private Sub Workbook_Open()
oldData = Sheets("Sheet1").Range("a1:a100").Value
End Sub

to Sheet module of Sheet1

Private Sub Worksheet_Calculate()
Dim a, i As Integer, x As Range
a = Range("a1:a100").Value
For i = 1 To 100
     If a(i,1) <> oldData(i,1) Then
          If x Is Nothing Then
              Set x = Cells(i,1)
          Else
              Set x = Union(x, Cells(i,1))
          End If
     End If
Next
If Not x Is Nothing Then
    Me.Activate
    x.Select
    oldData = Range("a1:a100").Value
End If
Set x = Nothing
End Sub

Save and close the workbook once and open again