Geek Answers Handbook

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

I need to clean a very complex Excel file.

To erase a cell, I need to make sure that it is not used somewhere in the workbook. I know that you can find out if a cell is used in the formula of another cell by going to the audience Formula> Trace, but this does not work if the cell is used in the following contexts:

  • Part of the range used for the drop-down list when checking data from another cell
  • In the formulaic part of the conditional formatting of another cell.

These two Excel functions are used a lot in the workbook.

Do you know a way to find out these dependencies?

+4
source share
1

- , 100%, ,

Sub DependantTest()

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

    Debug.Print i

Next i

End Sub


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

CF - , 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
+3

More articles:


All Articles