Seeing Formula Cells on Different Worksheets

Useful.

It might be worth adding that after the step in the quote below <F5> then <Enter> will get you back to the original cell containing your formula.n"Select one of the references, and when you click on OK (to dismiss the Go To dialog box), you are taken to that cell."

It should be obvious but:nDo not run any of the remove highlight macros if you didn't run the HighlightExtPrecedents first.

Here is the promised code:n=======================nnGlobal LastOrigin As Rangen'--------------------------------nSub HighlightExtPrecedents()n    Dim rOrigin As Rangen    Dim LinkN As Integern    n    ActiveSheet.ClearArrowsn    n    Set rOrigin = ActiveCelln    rOrigin.ShowPrecedentsn    n    On Error Resume Nextnn    Don        LinkN = LinkN + 1n        rOrigin.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1, LinkNumber:=LinkNn        If Err.Number <> 0 Then Exit Don        If rOrigin.Parent.Name = ActiveCell.Parent.Name Thenn            MsgBox "No precedents found on other sheets."n            Exit Don        End Ifn        CondHighlightn    Loopn    Set LastOrigin = rOriginn    n    ActiveSheet.ClearArrowsnnEnd Subn'--------------------------------nnPublic Sub RemoveLastHighlight()n    Dim rOrigin As Rangen    Dim LinkN As Integern    n    If LastOrigin Is Nothing Thenn        MsgBox "There is no record of precedent highlighting!"n        Exit Subn    End Ifn    ActiveSheet.ClearArrowsn    Set rOrigin = LastOriginn    rOrigin.ShowPrecedentsn    n    On Error Resume Nextnn    Don        LinkN = LinkN + 1n        rOrigin.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1, LinkNumber:=LinkNn        If Err.Number <> 0 Then Exit Don        If rOrigin.Parent.Name = ActiveCell.Parent.Name Then Exit Don        RemoveCondHighlightn    Loopn    n    n    ActiveSheet.ClearArrowsn    Set LastOrigin = NothingnnEnd Subn'--------------------------------nnSub RemovePrecedentsHighlight()n    Dim rOrigin As Rangen    Dim LinkN As Integern    n    ActiveSheet.ClearArrowsn    n    Set rOrigin = ActiveCelln    rOrigin.ShowPrecedentsn    n    On Error Resume Nextnn    Don        LinkN = LinkN + 1n        rOrigin.NavigateArrow TowardPrecedent:=True, ArrowNumber:=1, LinkNumber:=LinkNn        If Err.Number <> 0 Then Exit Don        If rOrigin.Parent.Name = ActiveCell.Parent.Name Then Exit Don        RemoveCondHighlightn    Loopn    Set LastOrigin = rOriginn    n    ActiveSheet.ClearArrowsnnEnd Subn'--------------------------------nnPublic Sub CondHighlight()n    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=TRUE"n    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPrioritynn    With Selection.FormatConditions(1).Interiorn        .Color = RGB(255, 200, 255)n    End Withn    Selection.FormatConditions(1).StopIfTrue = TruennEnd Subn'--------------------------------nnPublic Sub RemoveCondHighlight()n    Selection.FormatConditions(1).DeletenEnd Sub

The answer provided in Allens tip did not quite provide a solution for Jerry to be able to see all precedent cells on other sheets highlighted and visible in additional Excel windows. I have a macro solution for that. It is a bit extensive but hopefully would be useful for Jerry.nnI am proposing to use the conditional formatting to highlight the precedent cells. Regular background and borders could be used, but any conditional formatting already applied to the precedent cells would take precedence over any explicit formatting. Also, the conditional formatting must be applied in such a way that it will take precedence over any existing conditional formatting. As a bonus, when this particular highlighting rule for conditional formatting is removed, the original formatting, whether explicit or conditional is simply restored, without any need to store then recall the cell formatting that existed before highlighting.nnThe solution consists of several macros. Here is how it works:nnThe macro HighlightExtPrecedents will first show precedent arrows for the selected cell; if a range of cells was selected only the active cell will be used. I will call this cell “origin cell” in the rest of this description. The macro will follow the arrow to the precedent ranges or cells on other sheets. In my testing I found that all precedents from other sheets are pointed to by ArrowNumber 1. The remaining arrows point to precedents on the same sheet. This was tested in MS365 Family under Win10; older versions of Excel may behave differently regarding arrow numbers, but I have no access to them to test. Also, if there is no precedent from another sheet, the ArrowNumber 1 points to the first precedent on the same sheet; the macro tests for this and does not do any highlighting in such case, just displays a message: “No precedents found on other sheets.” nnThe second macro (RemoveLastHighlight) removes the highlighting that was ***last*** applied. This makes it easy to remove last highlighting without having to navigate to the origin cell. The location of the origin cell is stored in a Global variable LastOrigin by the HighlightExtPrecedents procedure and is used and reset to Nothing by the RemoveLastHighlight procedure. The content of the global variable may be lost in some situations (e.g., when the Reset button in VBA editor window is pressed or after a run-time error) and is also lost if the workbook is closed. The macro will notify the user of the fact there is no record about last highlighting. In such case the user should run a slightly different un-highlighting macro, as described next.nnThe macro RemovePrecedentHighlight unhighlights the precedents from other sheets using the same logic as highlighting. Before running the macro, the user ***must*** select the same cell that was selected as origin for highlighting, as it will remove border and background formatting for precedents of currently selected cell.n nHighlighting and removing highlight is meant to work in tandem to provide a temporary indication where the precedent cells are. If changes to formulas are made in between the two actions, or if the macros are not run in proper sequence, they may each act on wrong precedent cells and result in fouling up the highlighting.nnThe two additional subs: CondHighlight and RemoveCondHighlight, are called to apply or remove, respectively, conditional formatting to/from a cell or range selected by the loop in one of the main subroutines. Separation of CondHighlight from the main sub makes it easier for the user to modify the highlighting color and/or adding additional highlighting attributes like border.nnOne additional comment on using this solution with multiple monitors, or more precisely, multiple Excel windows: It works best if the origin cell is selected in Window #1. If it is in any other window, that window may return view to the origin sheet. Not really a big deal, but it is nicer if the highlighted cells stay visible on other sheets.nnI am going to post the code as my separate comment to be able to format it better.