Posts by IainM

    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


    End Sub

    Hope that helps!