How to highlight calculated fields in an Excel spreadsheet?

Question

Is there a simple way to do this, via macro or otherwise? By calculated field I mean a field that is computed from other fields, versus raw entered values. By highlight I mean colored differently. I need this to better understand a large spreadsheet from a client.

Answer 1

You can use the Interior.ColorIndex property to change the active cell's background color:

ActiveCell.Interior.ColorIndex = 36

You may also apply it to a range:

Range("A1:A5").Interior.Color = RGB(200,160,35)

This applies to Excel 2003, I haven't used the latest version but I doubt this has changed.

You can usually record a macro and then look at the generated code to see how something is done.

Answer 2

Simple solution: Ctrl - ` (the key just above Tab)

Answer 3

I'm going to assume you're only talking about cell formulas rather than VBA calculations here, since you could set the cell colour in your VBA procedure if you're doing it that way.

The way to do this is to check the cell for a formula after you're done with it, and change it's colour at that point. The relevant event here is Change, and the cell's HasFormula property will tell you whether the cell is a literal value, or calculated from a formula:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.HasFormula Then
        Target.Interior.Color = vbRed
    Else
        ' remove background colour entirely (i.e. No Fill)
        Target.Interior.ColorIndex = xlColorIndexNone
    End If
End Sub

Answer 4

The code below should cycle through each sheet, highlighting every cells that starts with an '=' and colors it the desired color (currently colour 36 which is Light Yellow).

Sub HighLightFormulas()
Dim objSheet As Worksheet
Dim strOriginalSheet As String
Dim intMaxBlankCells As Integer
Dim intBlankColumns As Integer
Dim intBlankRows As Integer
Dim intCurrentColumn As Integer
Dim intCurrentRow As Long

intMaxBlankCells = 40
strOriginalSheet = ActiveSheet.Name

For Each objSheet In Worksheets
    intBlankRows = 0
    intCurrentRow = 1
    intCurrentColumn = 1

    Do While intCurrentRow <= 65536 And intBlankRows <= intMaxBlankCells
        intBlankColumns = 0
        intCurrentColumn = 1

        Do While intCurrentColumn <= 256 And intBlankColumns <= intMaxBlankCells
          If Left(objSheet.Cells(intCurrentRow, intCurrentColumn).Formula, 1) = '=' Then
            objSheet.Cells(intCurrentRow, intCurrentColumn).Interior.ColorIndex = 36
          End If
          intCurrentColumn = intCurrentColumn + 1
        Loop

        If intCurrentColumn = intBlankColumns Then
            intBlankRows = intBlankRows + 1
        Else
            intBlankRows = 0
        End If
        intCurrentRow = intCurrentRow + 1
    Loop
Next objSheet
Worksheets(strOriginalSheet).Activate

Call MsgBox("The Highlighting process has completed", vbOKOnly, "Process Complete")

End Sub

It will also stop after 40 consecutive blank cells (to avoid processing all of a mostly blank sheet).

Hope this helps.

Answer 5

Excel has a built in feature of "Trace Dependents" (which shows arrows to show you the calculated cells)

Select the range containing your data.
Excel 2007 -> Formulas -> Trace Dependents

Answer 6

There was a previous thread on this subject: http://stackoverflow.com/questions/61432/tool-for-deciphering-spreadsheets/61448

Answer 7

To do it manually, press the F5 key to bring up the GoTo dialog. Click the Special Cells button. On the next screen, select Formulas (it's an option on the right).

Excel will select all of the cells that match. Now it's just a matter of applying formatting.

Answer 8

I liked Craig's code here, because it keeps the layout of the existing worksheet and yet shows what is calculated and what is not 'at a glance', but I have reworked it a bit so it does a better job of working out the active area of sheets, and I added an 'UnhighlightFormulas' subroutine so one can easily undo the formatting (e.g. before printing). It has been tested in Excel 2007. Note that you will lose any other cell background colouring upon running this.

Option Explicit
Public Sub HighlightFormulas()
ColorFormulas (36) '36 is yellow
End Sub

Public Sub UnhighlightFormulas()
ColorFormulas (-4142) '-4142 is default
End Sub

Private Sub ColorFormulas(intColor As Integer)
Dim wshSheet As Worksheet
Dim rngRange As Range
Dim rngCell As Range
For Each wshSheet In Worksheets
  Set rngRange = RangeInUse(wshSheet)
  If Not rngRange Is Nothing Then
    For Each rngCell In rngRange
      If Left(rngCell.Formula, 1) = "=" Then
        If rngCell.Interior.ColorIndex <> intColor Then rngCell.Interior.ColorIndex = intColor
      Else
        If rngCell.Interior.ColorIndex <> -4142 Then rngCell.Interior.ColorIndex = -4142 '-4142 is default
      End If
    Next
  End If
Next
End Sub

Private Function RangeInUse(ws As Worksheet) As Range
  Dim LastRow&, LastCol%
' adapted from http://www.beyondtechnology.com/geeks012.shtml
' Error-handling in case there is no data in worksheet
  On Error Resume Next
  With ws
    LastRow& = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    LastCol% = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
  End With
  Set RangeInUse = ws.Range("A1", Cells(LastRow&, LastCol%))
End Function