Have you checked out the Dependents property? This
property returns a Range object that represents the range
containing all the dependents of a cell. Related is the
DirectDependents property. Also, in case you missed it,
there is a Track Changes feature listed in the Tools menu
(Excel 97). I've never used it nor the equivalent in Word
so I can't say it's appropriate.

Regards,
Greg


-----Original Message-----
I am writing a macro to simuulate what Track Changes does
in Word. When a user modifies a cell, I apply special
formatting to that cell to visually indicate that the
cell has been modified. That part is done. The next
challenge is that I have a number of sheets in my
workbook. Some sheets are read-only (protected) and some
are read/write. The read-only sheets have cells that
are "linked" to the read/write sheets via formulas. When
a cell is modified (or a range), I want to apply the
formatting to any cells that are linked to the source
cells. The way in which I do think linking is as follows:

I create a named range that includes all the editable
cells (e.g., foo). I then write a formula (=foo) within
each of the cells on the read-only tabs that fall within
the same address as the source range.

Ideally, I'm looking for a way to ask Excel which cells
are being re-calculated as a result of a change (though,
even if this were possible, I'm not sure it will help
because of the way in which I am linking cells. I fear
that all cells in the range will be recalculated, even
though only one cell may have been modified).

The only solution I have so far is a fragile one, based
on assumptions. If a cell is modified, I first figure out
which range it falls within (I can iterate through each
named range via the Application.Names collection). Once I
have this info, I can then use a Select Case to figure
out which "downstream" tabs I will have to apply my
specicial formatting to. Of course, if in the future I
add additonal tabs or remove some, this will fall apart.

Thoughts?
.