Try something like this. The sub below should mark any cells in TestRange that other cells depend on in magenta (thanks to Ed for earlier colour code). Only problem (which is a limitation of the Excel Dependents property) is that it won't find dependencies on other worksheets. You need to do much more work for that.
Sub MarkDependentCells(ByRef TestRange As Range)
'used to test
Dim t_range As Range
Dim this_cell As Range
'ok, loop through each cell
For Each this_cell In TestRange.Cells
'now test for dependents
Set t_range = Nothing
On Error Resume Next
Set t_range = this_cell.Dependents
On Error GoTo 0
'if there are any dependents then mark the cell in magenta
If Not (t_range Is Nothing) Then
this_cell.Interior.ColorIndex = 7
End If
Next
End Sub
Hope that helps!