I am new to VBA and I have been struggling to build a macro that accurately identifies cells in the selected range that are referenced by formulas in other sheets and selects only those cells that fulfill that criteria.

the macro I have been able to build with help returns mixe results, sometimes returning cells that dont have any direct dependents in other sheets, and sometimes not selecting those.

please help me fixing the code and understand what went wrong.

Sub FindDependentsInOtherSheets()
    Dim ws As Worksheet
    Dim cell As Range
    Dim rng As Range
    Dim wsSource As Worksheet
    Dim formulaCell As Range
    Dim outputRange As Range
    Dim dependentFound As Boolean
    Dim formulaText As String
    Dim formulaCells As Range
    Dim cellAddress As String

    Set rng = Selection
    If rng Is Nothing Then
        MsgBox "Please select a range first."
        Exit Sub
    End If

    Set wsSource = rng.Worksheet

    ' Initialize outputRange to Nothing
    Set outputRange = Nothing

    ' Loop through each cell in the selected range
    For Each cell In rng
        If IsEmpty(cell) Then
            GoTo NextCell
        End If
        
        dependentFound = False
        cellAddress = "'" & wsSource.Name & "'!" & cell.Address(False, False)
        
        ' Loop through each worksheet in the active workbook to find formulas referencing the cell
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> wsSource.Name Then
                On Error Resume Next
                Set formulaCells = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
                On Error GoTo 0
                If Not formulaCells Is Nothing Then
                    For Each formulaCell In formulaCells
                        formulaText = formulaCell.Formula
                        ' Check if the formula references the cell
                        If InStr(1, formulaText, cellAddress, vbTextCompare) > 0 Then
                            dependentFound = True
                            Exit For
                        End If
                    Next formulaCell
                End If
                If dependentFound Then Exit For
            End If
        Next ws
        
        ' If a dependent is found in another sheet, add the cell to the output range
        If dependentFound Then
            If outputRange Is Nothing Then
                Set outputRange = cell
            Else
                Set outputRange = Union(outputRange, cell)
            End If
        End If

NextCell:
    Next cell

    ' Select the output range if any cells found
    If Not outputRange Is Nothing Then
        outputRange.Select
        MsgBox "Cells that are direct precedents to other sheets have been selected.", vbInformation
    Else
        MsgBox "No direct precedents found in other sheets for the selected range.", vbInformation
    End If

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

I exepected to loop through each cell in the selected range and check if there are any formulas in other sheets that reference the cells in my selected range. If a dependent is found, we’ll add the cell to the output range. Finally, if any cells are found, we’ll select the output range.

the end result thus, is the selection of cells from my selected range that had dependents