Coloring Linked Cells

No matter what firm you work at, best practices and common sense dictate that you color hard-coded numbers, formulae and links to other worksheets using distinct colors.  We have discussed the first two, which are easily accomplished using F5, ALT + S to select either constants or formulae (Microsoft says formulas; they're apparently interchangeable).

Unfortunately, Excel does not have a built-in command to highlight all cells linking to other worksheets.  That means if you are working late at night on the Sunday-Monday morning of Labor Day weekend and need to color all other-sheet links to green in a spreadsheet with thousands of rows mixing numbers, formulas and other-sheet links, YOU ARE FUCKED.  Until now.

With the help of my some times enemy and other times friend visual basic, you can do what F5 does, but to select formulas with links to other sheets.  Remember, I am not a VBA expert, but I know how to use Google and combine other people's VBA code.  Here is what to do:

1. ALT + F11 to open up Microsoft Visual Basic within Excel
2. ALT, I, M to insert a new module
3. Within the module code editing window, paste in: 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.Select
   End If
   
   Set TestRange = Nothing
   Set MyRange = Nothing
   End Sub 
4. F5, ALT + R to run the module on each desired sheet

Notice that all the formulae with links to other sheets are highlighted.  See for yourself by hitting CTRL + ~.  Now, all you need to do is color the highlighted cells!  You can also hit ALT, F, E to save the module for future use.

So how did this work?  Imagine that a human was doing this exercise.  He could use CTRL + F to find each cell containing links to other sheets by searching for "!" within each formula.  He could then select each cell that contained "!" cumulatively (using either the keyboard and SHIFT + F8 or the mouse and CTRL).  Excel does all of that almost instantaneously.  The only problem would be if you write footnotes like, "Assumes leverage of 3.5x!!!!" because that's getting highlighted too.  Anyway, imagine the spreadsheet is huge and contains a mix of constants and other formulae.  That could have been a 15-minute exercise, but will now take 2 seconds!

Speaking of taking 2 seconds, I decided to write this post while thinking of ways to satisfy Mrs. F-One.  Happy Labor Day Weekend.

-F-One