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:
First, in the sample worksheet, these formulas, with relative references, were entered in row 5:
Next, the formula in C5 was copied to the right, into cell D5. The relative cell reference changed, from B5 to C5.
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.
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:
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:
To use the code from the sample file, follow these steps:
After you add the formula cell colour code macro, named GetFormulae, to your workbook, follow these steps to run the macro.
The active sheet is copied to a new workbook, and the formula cell colour codes are applied there.
The original sheet is not changed.
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.
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: July 11, 2021 10:14 AM