Formula Cell Colour Code Macro

Use this macro to quickly audit the formula cells on a worksheet. The macro colour codes formula cells, and marks cells where those formulas have been copied across, or down, or both. To audit, just check the solid colour cells, and confirm that they were copied across and down correctly. Thanks to Dermot Balson, who created this macro.

NOTE: The macro makes a copy of the original sheet, so the colour coding doesn't affect your Excel file.

Introduction

This macro colour codes the formula cells on a copy of your Excel worksheet, so you can quickly confirm that things are set up correctly.

For example, this sample sheet has formulas in columns C, D and E.

When you run the macro, it creates a copy of that sheet, in a new workbook. The formula cells are colour coded as solid (formula), vertical lines (copied down), horizontal lines (copied right), or grid (copied right and down).

To audit the sheet, you would:

• Check the formulas in cells C5 and E5 on the original worksheet
• Look at the colour coded cells, to ensure that the formulas were copied down/across to the correct cells.

Worksheet Formulas

First, in the sample worksheet, these formulas, with relative references, were entered in row 5:

• C5:  =B5*10
• E5:  =SUM(B5:D5)

Copy Formula Across

Next, the formula in C5 was copied to the right, into cell D5. The relative cell reference changed, from B5 to C5.

• D5:  =C5*10

Copy Formulas Down

Next, the 3 formulas in row 5 were copied down to row 11, to fill the grid. The row number in the relative references changed for each row.

Sample Sheet with Colour Codes

If you run the colour code macro with the sample sheet active, the colour coded cells will show the following results.

• Solid Fill: Cells C5 and E5 have the original formulas
• Vertical Lines: Cells C6:C11 and E6:E11 were copied down from row 5
• Horizontal Lines: Cell D5 was copied right from cell C5
• Grid Pattern: Cells D6:D11 were copied right and down from cell C5

Colour Code Macro

Here is the macro code, named GetFormulae, from Dermot Balson, to colour code the formula cells on the active sheet. There are extensive comments throughout the code, explaining how it works.

In the following sections, there are instructions for adding the code to your workbook, and steps for running the macro.

NOTE: In Dermot's code, I added a section to convert all named tables to ranges. Otherwise, Dermot's line that unmerges the cells will cause an error, if there are any named tables on the active sheet..

'=====================
'Find and mark up formulae
'in sheet or workbook
' by Dermot Balson
'posted on contextures.com
'=====================
'markings show whether formulae are copied
'from the left or above, both, or neither
'It makes a copy of the active worksheet
'(optionally instead you can
'provide a worksheet name)
'then it checks whether
'cells containing formulae
'have been copied from the left or above
'cells copied from the left are
'given horizontal shading
'cell copied from above are
'given vertical shading
'cells copied from the left and above
'(ie their formula is what you get by
'copying from left or above)
'are cross hatched
'formula cells which have not
'been copied are given a solid colour
Sub GetFormulae()
Dim F As Variant, F2 As Variant
Dim A As String, rng As Range
Dim P() As Long, wbkName As String
Dim i As Long, j As Long
Dim r As Long, c As Long
'create codenames to make
'the code more readable
Dim vbLeft As Long
Dim vbAbove As Long, vbNone As Long
vbLeft = 1: vbAbove = 2: vbNone = 4
'set the colours and shading
Dim patterncolor As Long
Dim colorNone As Long
Dim mylist As ListObject

patterncolor _
  = 16773571: colorNone = 9486586

'turn everything off for speed
Application.ScreenUpdating = False
Application.Calculation _
  = xlCalculationManual
Application.EnableEvents = False

'copy the sheet to a new workbook
'so we can scribble on it
wbkName = ActiveWorkbook.Name
ActiveSheet.Copy
If wbkName = ActiveWorkbook.Name Then
  MsgBox "Copying sheet to a new workbook" _
   & vbCrLf _
   & "(so we can colour code it) failed." _
   & vbCrLf & vbCrLf _
    & "Unable to continue", vbCritical
  Exit Sub
End If

'change any tables to ranges
'can't unmerge with tables on sheet
With ActiveSheet
If .ListObjects.Count > 0 Then
  For Each mylist In .ListObjects
    mylist.Unlist
  Next mylist
End If
End With

'merged cells break this code,
'get rid of them
ActiveSheet.Cells.UnMerge
Cells.Interior.Color = xlNone
A = ActiveSheet.UsedRange.Address
'store array of cell formulae
F = Range(A).Formula
r = UBound(F, 1)
c = UBound(F, 2)
ReDim P(r, c)

'loop through rows and columns
'for each cell, try copying it to the
'right and down and if that gives the same
'formula that is already there, store the
'result in the array A
'note we are storing the result
'for the cell we copied to, so if we
'are in the 3rd row, 2nd column, and
'we copy down, and it matches
'the formula there, we will store
'the result in A(4,2) not A(3,2)
'what we store is a code (1 or 2) that
'will tell VBA that it worked, then when
'we've finished copying right and down,
'we look at what in in the array A for the
'cell we are in, and colour it accordingly
'so if we are in the 3rd row, 2nd column,
'we will see what is in A(3,2)
If c > 1 Then
  With Range(A)
    .Cells(1, 1).Resize(r, c - 1).Copy
    .Cells(1, 2).PasteSpecial _
      Paste:=xlPasteFormulas
    F2 = .Formula
  End With
  For i = 1 To r
    For j = 2 To c
      If Left$(F(i, j), 1) = "=" Then
        If F(i, j) = F2(i, j) _
          Then P(i, j) = vbLeft
      End If
    Next j
  Next i
  Range(A).Formula = F
End If

If r > 1 Then
  With Range(A)
    .Cells(1, 1).Resize(r - 1, c).Copy
    .Cells(2, 1).PasteSpecial _
        Paste:=xlPasteFormulas
    F2 = .Formula
  End With
  For i = 2 To r
    For j = 1 To c
      If Left$(F(i, j), 1) = "=" Then
        If F(i, j) = F2(i, j) Then
          P(i, j) = P(i, j) + vbAbove
        ElseIf P(i, j) = 0 Then
          P(i, j) = 4
        End If
      End If
    Next j
  Next i
  Range(A).Formula = F
End If

Set rng = Range(A)
rng.Interior.patterncolor = 6737151
For i = 1 To r 'loop rows
  For j = 1 To c
    With rng.Cells(i, j).Interior
      'colour the cell we are in
      'based on what we've stored in A
      Select Case P(i, j)
      Case vbLeft
        .Pattern = xlLightHorizontal
        '.PatternColor = 6737151
      Case vbAbove
        .Pattern = xlLightVertical
        '.PatternColor = 6737151
      Case vbLeft + vbAbove
        .Pattern = xlGrid
        '.PatternColor = 6737151
      Case vbNone
        .Color = colorNone
      End Select
    End With
  Next j
  'give Windows chance to catch up
  DoEvents
Next i

'turn everything back on
Application.CutCopyMode = False
Cells(1, 1).Select
Application.Calculation _
  = xlCalculationAutomatic
Application.EnableEvents = True
Application.StatusBar = False

End Sub

Add Code to Workbook

To add the GetFormulae macro to your workbook, follow the steps in one of these videos:

1. Copy code from this page, and add to your workbook
2. Copy code from sample file into your workbook

NOTE: You can add the code to a workbook that you want to audit frequently. Or, add the code to an Excel file that is always open, such as your Personal Workbook file, or an add-in file with your favourite macros.

Option 1: Copy code from this page

Option 2: Copy code from sample file

1. Add Code From This Page

To use the code from this page, follow these steps:

1. Copy the GetFormulae macro code shown above
2. Open the workbook in which you want to add the code
3. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
4. Choose Insert | Module
5. Where the cursor is flashing, choose Edit | Paste

2. Add Code From Sample File

To use the code from the sample file, follow these steps:

1. Download and open the sample file (in the Download section below)
2. Open the workbook in which you want to add the code
3. Save that workbook as xlsm or xlsb format, to allow macros
4. Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
5. In the Project Explorer, find your workbook, and sample file that you downloaded.
• In this example, the code is in formulacolourcode_db.xlsm and will be copied to filewithformulas.xlsm
6. In the workbook with the code, click the + sign to view the list of Modules
7. Click on the module that you want to copy, and drag it over the project where you'd like the copy placed.
   1. In this example, the modColour module is being copied
8. Release the mouse button, and a copy of the module will appear in the workbook.

Run the Colour Code Macro

After you add the formula cell colour code macro, named GetFormulae, to your workbook, follow these steps to run the macro.

1. Go to the sheet whre you want to colour code the formula cells.
2. On the Excel Ribbon, click the View tab
3. At the far right, click Macros
4. Select the GetFormulae macro in the list
5. Click the Run button

The active sheet is copied to a new workbook, and the formula cell colour codes are applied there.

The original sheet is not changed.

More Formula Audit Tips

For more formula auditing tips, watch this short video. There are written steps on the Auditing Excel Formulas page.

About the Developer

Dermot Balson is a retired actuary, who spends time doing family research -- with spreadsheets, of course!

Get the Sample File

To see the sample data, and to test the macro, get the Formula Cell Colour Code Macro sample file. There are two sheets with formulas, for testing.

• One sheet is the simple example shown on this page.
• The other sheet has a named Excel table, and more formulas outside the table.

The zipped file is in xlsm format, and contains the macro from this page. Be sure to enable macros when you open the workbook, if you want to test the macro.

More Tutorials

Excel Worksheet Macros

Formula Audit Tips

Add Your Macros to Ribbon Tab

List & Close Workbooks

Workbook Backup Tool, Free

Save Sheets As PDF Format