Option Explicit
Sub FormatOtherSheetLinks()
Dim TestRange As Range, C As Range, MyRange As Range
Dim FirstAddress As String, SheetString As String
Dim wsh As Worksheet
On Error Resume Next
Set TestRange = Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not TestRange Is Nothing Then
For Each wsh In ActiveWorkbook.Worksheets
SheetString = wsh.Name & "*!"
SheetString = Replace(SheetString, "'", "''")
With TestRange
Set C = .Find(SheetString, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False)
If Not C Is Nothing Then
FirstAddress = C.Address
If MyRange Is Nothing Then Set MyRange = C
Do
Set C = .FindNext(C)
If Not MyRange Is Nothing Then Set MyRange = Union(MyRange, C)
Loop Until C.Address = FirstAddress
End If
End With
Next
If Not MyRange Is Nothing Then MyRange.Interior.Color = vbBlue
End If
Set TestRange = Nothing
Set MyRange = Nothing
End Sub
|