VBA to analyze formula and highlight precedents on all sheets

The built-in functionality allows you to double-click or click F2 and see all precedents highlighted, but only on the same sheet as the formula. This solution applies fill color to all immediate precedent cells on all sheets, and presents a UserForm to select which sheet to activate. Then closing the form restores the original formatting.

See release notes for limitations. Also, it shows only immediate precedents, and not precedents of precedents.

Consider this a beta test. I would be happy to entertain suggestions for corrections and enhancements.

Update. Functionality is the same. Code has been updated to use a single regexp pattern that will cover both formats of sheet names. Also added a note that this will not parse 3D range references.

Why are you starting a formula with a "+" sign?

It says this in the opening line:

The built-in functionality allows you to double-click or click F2 and see all precedents highlighted, but only on the same sheet as the formula.

Your formula is referencing other sheets.

Ali, that note refers to the built-in functionality. My version should work. I'll take a look at that example.

Oh, OK - my misunderstanding.

Originally Posted by leekosal

nice VBA code. However, the code fails with just basic references. Eg., =+Sheet3!B7+Sheet2!B4
How to fix this?
Thank you

There seems to be an error in the regexp pattern for detecting references to external sheets if they are not enclosed in 'single quote' marks. I'll look into that.

Fixed bug in regex expression that now recognized sheet names if not enclosed in 'single quotes'. Note that this will not work for expressions that cannot occur in Excel formulas, like sheet names with special characters that are not in 'single quotes'.

Here is what I think is a better solution. This gives you a list of all the range references and allows you to pick one to navigate to. This actually selects the ranges instead of highlighting them, which I think will be more useful in most cases.