How to find all dependencies of a cell in Excel (also conditional formatting and data validation)

Question

I need to cleanup a very complex Excel file.

To be able to erase a cell, I need to make sure it's not used somewhere in the workbook. I know it is possible to find out if a cell is used in another cell's formula by going in Formula auditing > Trace dependents but this does not work if the cell is used in the following contexts :

• Part of a range used for a drop-down list in the Data validation of another cell
• In a formula part of the Conditional formatting of another cell.

These 2 Excel features are used a lot in the workbook.

Do you know a way to found out those dependencies ?

Answer 1

Try something like this, not 100%, but looks like it works from simple tests

Sub DependantTest()

For Each i In DependantOnValidation(Range("A1"))

    Debug.Print i

Next i

End Sub

Using this function

Function DependantOnValidation(rngLookAt As Excel.Range) As Collection

Dim ws As Worksheet Dim rInspect As Range Dim rWorking As Range Dim rIntersect As Range

Set ws = ThisWorkbook.Worksheets("sheet1")

On Error Resume Next

Set DependantOnValidation = New Collection

For Each rInspect In ws.Range("a1:z1")

    Set rWorking = Range(Replace(rInspect.Validation.Formula1, "=", vbNullString))

    If Not rWorking Is Nothing Then

        Set rIntersect = Application.Intersect(rngLookAt, rWorking)
        DependantOnValidation.Add rInspect.Address

    End If

    Set rWorking = Nothing
     Next rInspect

End Function

and for CF something like this, not complete tho

If rInspect.FormatConditions.Count > 0 Then
    For Each fCondition In rInspect.FormatConditions
        If fCondition.Formula1 <> "" Then
            If InStr(1, fCondition.Formula1, rngLookAt.Address(True, True)) > 0 Or _
                    InStr(1, fCondition.Formula1, rngLookAt.Address(False, True)) > 0 Or _
                        InStr(1, fCondition.Formula1, rngLookAt.Address(True, False)) > 0 Then

            End If
        End If
    Next fCondition
End If