It took a little studying to figure out what was going on (Tom's post about
removing the arrows put me on what I think was the right track). Give this
macro a try; I think it will do what you want...

Sub SelectNonDependentCellsInSelection()
Dim ShapeCount As Long
Dim R As Range
Dim NonDependents As Range
ActiveSheet.ClearArrows
ShapeCount = ActiveSheet.Shapes.Count
For Each R In Selection
R.ShowDependents
If ActiveSheet.Shapes.Count = ShapeCount Then
If NonDependents Is Nothing Then
Set NonDependents = R
Else
Set NonDependents = Union(R, NonDependents)
End If
End If
ActiveSheet.ClearArrows
Next
NonDependents.Select
End Sub

Rick


"rajesh" wrote in message
...
Hi Tom

You are correct what i wanted was tracing dependents on other sheets (not
only active sheet.

Your code is working perfectly. but works for entire sheet, i need only
selected range to be traced.

Thanks for your help.

"TomPl" wrote:

VBA cannot recognize remote dependencies (i.e. it only recognizes
dependencies on the active sheet). The best solution I can think of is
to
show dependencies for all cells on the worksheet because that will show
remote dependencies as well. Manually this would be a tediuos task but
this
code will do it for you.

Sub CellsDeps()

Dim rng As Range

For Each rng In ThisWorkbook.ActiveSheet.UsedRange
rng.ShowDependents
Next rng

End Sub

Of course you can "Unshow" all the dependencies with Tools - Formula
Auditing - Remove All Arrows.

Hope this helps.



"rajesh" wrote:

Hi

I would like to highlight cells that are not having any dependents
(i.e.,
any other cell in the same worksheet or another worksheet does not
depend on
the cell to be validated).

any possible solution through vba / macro is of great help.

thanks in advance