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.

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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 ?