Sub FindItAll()
    Dim oSheet As Object
    Dim Firstcell As Range
    Dim NextCell As Range
    Dim WhatToFind As Variant
    WhatToFind = Application.InputBox("What are you looking for ?", "Search", ,  100 ,  100 , , ,  2 )
        If WhatToFind <> "" And Not WhatToFind = False Then
            For Each oSheet In ActiveWorkbook.Worksheets
            oSheet.Activate
            oSheet.[a1].Activate
            Set Firstcell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                If Not Firstcell Is Nothing Then
                Firstcell.Activate
                MsgBox ("Found " & Chr( 34 ) & WhatToFind & Chr( 34 ) & " in " & oSheet.Name & "!" & Firstcell.Address)
                On Error Resume Next
                    While (Not NextCell Is Nothing) And (Not NextCell.Address = Firstcell.Address)
                    Set NextCell = Cells.FindNext(After:=ActiveCell)
                        If Not NextCell.Address = Firstcell.Address Then
                        NextCell.Activate
                        MsgBox ("Found " & Chr( 34 ) & WhatToFind & Chr( 34 ) & " in " & oSheet.Name & "!" & NextCell.Address)
                        End If
                    Wend
                End If
            Set NextCell = Nothing
            Set Firstcell = Nothing
            Next oSheet
        End If
End Sub

Dim Rng As Range
Dim NameRange As Range
Set NameRange = ActiveWorkbook.Names("TheName").RefersToRange

On Error Resume Next
For Each Rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    Err.Clear
    If Not Application.Intersect(Rng.DirectPrecedents, NameRange) Is Nothing
Then
        If Err.Number =  0  Then
            Debug.Print "Cell: " & Rng.Address & " refers to " &
NameRange.Address
        End If
    End If
Next Rng