Marking cells without dependents

I tripped on this in a forum today...

The user wanted to create a message for all cells that did not have any cells dependent on their target.  The set about doing this by using the Trace Dependents tool through VBA, and logically set it up to try to trace the dependent and react to an error if there wasn't one.  This is where things got interesting, as this object does not throw errors!

The routine below allows you to check a range of cells and it marks all those cells which do not have dependents.  The method is to trigger the Trace Dependents command, follow to the first dependent, then check the cell reference of the active cell vs the one that you started from.  If they are the same, then you haven't got any dependent cells.  If not, then navigate back and check the next cell.

[vb]Sub Marked_Dependent_Free_Cells()

Dim rngCheck As Range
Dim wsSource As Worksheet
Dim rngSource As Range
Set wsSource = ActiveSheet

'Record range to check (otherwise will be lost)
Set rngSource = Selection

'Check each cell in area to check
For Each rngCheck In rngSource

'Show dependent cells (if any) and navigate to the first one
With rngCheck
.ShowDependents
.NavigateArrow False, 1, 1
End With

'Check if navigation was successful
If ActiveSheet.Name = wsSource.Name And ActiveCell.Address = rngCheck.Address Then

'Still on same sheet, so no dependents found.
'Colour cell green
rngCheck.Interior.ColorIndex = 35

Else

'On new sheet, so navigate back
With ActiveCell
.ShowPrecedents
.NavigateArrow True, 1, 1
End With
End If
Next rngCheck
End Sub[/vb]

It was written and tested in Excel 2007, but should work in earlier versions. 🙂