Option Explicit

Sub Findref()
    Dim regexref As RegExp
    Set regexref = New RegExp
    regexref.Global = True
    regexref.IgnoreCase = True
    regexref.Pattern = "#REF"

    Dim regexfile As RegExp
    Set regexfile = New RegExp
    regexfile.Global = True
    regexfile.IgnoreCase = True
    regexfile.Pattern = "C:"

    Dim x As Name
    For Each x In ActiveWorkbook.Names
        If regexref.Test(x.RefersTo) Or regexfile.Test(x.RefersTo) Then
            Debug.Print x.Name
        End If
    Next x
End Sub