VBA to Check if Cell Formula References Another Sheet

Hello,

I am trying to use VBA to determine if a formula within a given cell references another sheet. Suppose I am on A1 on Sheet1 and the formula in that cell is "=sum(A2+Sheet2!A2). I want to be able to return something (doesn't matter what) that lets me know that the formula references a different sheet.

Thank you for any help that can be provided.

learning_vba

This shades any formula cells referencing other sheets to yellow.

Sub HighlightFormulaswithSheetRefs()
Dim Rng As Range
    On Error Resume Next
        Sheet6.Select
        Application.ScreenUpdating = False
            Application.Calculation = xlManual
            ActiveSheet.Calculate
            For Each Rng In Sheet6.Cells.SpecialCells(xlCellTypeFormulas)
                Rng.Select
                If InStr(1, ActiveCell.Formula, "Sheet", vbTextCompare) > 0 Then
                    ActiveCell.Interior.ColorIndex = 6
                End If
            Next Rng
            Application.Calculation = xlAutomatic
        Application.ScreenUpdating = False
    On Error GoTo 0
End Sub

Hi, Andy,

too much Selection in the code in order to work with ActiveCell (who needs to do this except the macro recorder) - you should be able to do better than that (thereīs no Select necessary, and action may be taken from another sheet). To my opinion not the best of code examples ever.

Ciao,
Holger

Hi, learning_vba,

what Andy used is the codename for the sheet, not the one on the worksheet tab.

If you donīt know the name you could refer to ActiveSheet for the sheet the code is run on or loop through the worksheets in the workbook and rely on them without having to know the names:

Sub HighlightFormulaswithSheetRefsActSheet()

Dim Rng As Range

With Application
  .ScreenUpdating = False
  .Calculation = xlManual
End With

On Error Resume Next
For Each Rng In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    If InStr(1, Rng.Formula, "Sheet", vbTextCompare) > 0 Then
        Rng.Interior.ColorIndex = 6
    End If
Next Rng

With Application
  .Calculation = xlAutomatic
  .ScreenUpdating = False
End With

On Error GoTo 0

End Sub

Sub HighlightFormulaswithSheetRefsAllWks()

Dim Rng As Range
Dim wks As Worksheet

With Application
  .ScreenUpdating = False
  .Calculation = xlManual
End With

On Error Resume Next
For Each wks In Worksheets
  For Each Rng In wks.UsedRange.SpecialCells(xlCellTypeFormulas)
      If InStr(1, Rng.Formula, "Sheet", vbTextCompare) > 0 Then
          Rng.Interior.ColorIndex = 6
      End If
  Next Rng
Next wks

With Application
  .Calculation = xlAutomatic
  .ScreenUpdating = False
End With

On Error GoTo 0

End Sub

Cioa,
Holger

Holger it was based on a sub to convert functions to values.. so i don't really care

VBA - do you want the code to iterate through all the worksheets ?