Sunday, September 5, 2010

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