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.

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.

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

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

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**

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.

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

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

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

- Copy code from this page, and add to your workbook
- 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.

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

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

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

- Download and open the sample file (in the Download section below)
- Open the workbook in which you want to add the code
- Save that workbook as xlsm or xlsb format, to allow macros
- Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
- 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
- In the workbook with the code, click the + sign to view the list of Modules
- Click on the module that you want to copy, and drag it over the project
where you'd like the copy placed.
- In this example, the modColour module is being copied

- Release the mouse button, and a copy of the module will appear in the workbook.

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

- Go to the sheet whre you want to colour code the formula cells.
- On the Excel Ribbon, click the View tab
- At the far right, click Macros
- Select the
**GetFormulae**macro in the list - 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.

This short video shows another way to troubleshoot a worksheet, without macros.

The written steps are on the Find and Fix Formula Cells page.

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

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

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.

Last updated: April 5, 2024 11:18 AM