Tracing dependents - Excel 2003

M

Michelle

Is there a way that I can highlight all the cells in a worksheet that contain
values that are used by formulas in other worksheets/workbooks?
 
K

Ken Wright

Perhaps this, although it will show all dependents on-sheet as well and not
just those off-sheet

Sub ShowDeps()

Dim myrng As Range
Dim myrng1 As Range
Dim myrng2 As Range

With ActiveSheet
Set myrng1 = Intersect(.UsedRange, _
.UsedRange.SpecialCells(xlCellTypeFormulas, 23))
Set myrng2 = Intersect(.UsedRange, _
.UsedRange.SpecialCells(xlCellTypeConstants, 23))
End With

Set myrng = Union(myrng1, myrng2)

For Each cel In myrng
cel.ShowDependents
Next cel
End Sub
 
Top