- Excel Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
This code will make a copy of the active sheet and then mark up the formulae, using shading to show which ones have been copied, and from where. It works from left to right, top to bottom.
Solid colour = this cell has NOT been copied from left or above, ie it is new
Horizontal hatch = this cell has been copied from the left
Vertical hatch = this cell has been copied from above
Cross hatch = this cell has been copied from the left+above
The idea is to make it much easier to check a complex sheet, because you only have to check the solid coloured cells and then confirm they have been copied correctly. If there is a cell with a corrupted formula in the middle of a table, it will be a very obvious solid colour.
For obvious reasons, the code makes a copy of the sheet, and then it literally looks at each cell, copying in the formula first from left, then from above, to see if it gives the same result. This is a bit slow, but it is the only sure way to check that formulae have been copied identically.
An example is given below, including a "rogue" cell in the middle of the table with a different formula.
Solid colour = this cell has NOT been copied from left or above, ie it is new
Horizontal hatch = this cell has been copied from the left
Vertical hatch = this cell has been copied from above
Cross hatch = this cell has been copied from the left+above
The idea is to make it much easier to check a complex sheet, because you only have to check the solid coloured cells and then confirm they have been copied correctly. If there is a cell with a corrupted formula in the middle of a table, it will be a very obvious solid colour.
For obvious reasons, the code makes a copy of the sheet, and then it literally looks at each cell, copying in the formula first from left, then from above, to see if it gives the same result. This is a bit slow, but it is the only sure way to check that formulae have been copied identically.
An example is given below, including a "rogue" cell in the middle of the table with a different formula.
VBA Code:
Sub MarkFormulae()
Dim V As Variant, rng As Range, S As Worksheet
Dim i As Long, j As Long, r As Long, C As Long, ii As Long, jj As Long, n As Long, skip As Boolean
Dim vbLeft As Long, vbAbove As Long
vbLeft = 1: vbAbove = 2
Dim colorLeft As Long, colorAbove As Long, colorBoth As Long, colorNone As Long
colorLeft = 16773571
colorAbove = 10092543
colorBoth = 6750054
colorNone = 9486586
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.Copy
Set S = ActiveSheet
S.Cells.UnMerge
Cells.Interior.Color = xlNone
V = Range(Cells(1, 1), S.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1)).Formula
r = UBound(V, 1)
C = UBound(V, 2)
ReDim A(r, C) As Long
For i = 1 To r - 1
Application.StatusBar = "Processing " & S.Name & ": row " & i & " of " & r
For j = 1 To C - 1
If Left$(V(i, j), 1) = "=" Then
Cells(i, j).Copy
Cells(i, j + 1).PasteSpecial Paste:=xlPasteFormulas
If Cells(i, j + 1).Formula = V(i, j + 1) Then
A(i, j + 1) = A(i, j + 1) Or vbLeft
End If
Cells(i, j + 1).Formula = V(i, j + 1)
Cells(i, j).Copy
On Error Resume Next
Cells(i + 1, j).PasteSpecial Paste:=xlPasteFormulas
skip = (Err.Number <> 0)
On Error GoTo 0
If skip = False Then
If Cells(i + 1, j).Formula = V(i + 1, j) Then
A(i + 1, j) = A(i + 1, j) Or vbAbove
End If
Cells(i + 1, j).Formula = V(i + 1, j)
Select Case A(i, j)
Case vbLeft
Cells(i, j).Interior.Pattern = xlLightHorizontal
Cells(i, j).Interior.PatternColor = 6737151
Case vbAbove
Cells(i, j).Interior.Pattern = xlLightVertical
Cells(i, j).Interior.PatternColor = 6737151
Case vbLeft + vbAbove
Cells(i, j).Interior.Pattern = xlGrid
Cells(i, j).Interior.PatternColor = 6737151
Case Else
Cells(i, j).Interior.Color = colorNone
End Select
End If
End If
Next j
DoEvents
Next i
Application.CutCopyMode = False
Cells(1, 1).Select
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.StatusBar = False
End Sub
