Macro to highlight cells with differing formulas

**Quercus** · 09-13-2022, 08:37 PM

I have a large and complex workbook. For the most part, every row contains the same formula with relative references copy and pasted to the right. I.e. A3 = A$1 * A2, A4 = A$1 * A3, A5 = A$1 * A4, etc.

However, there are some cells where the formula will change. Sometimes intentionally, and sometimes in error.

I want a way to highlight any cell where the formula changes so I can check the workbook for errors or correct a change I no longer need. I.e., highlight A5 in this example: A3 = A$1 * A2, A4 = A$1 * A3, A5 = A4 * B7.

Short of writing my own routine to parse the starting formula (A3 = A$1 * A2) and computing the resulting formula (A4 = A$1 * A3) and comparing that to the actual formula in A4, is there a way to make Excel do this for me? I was thinking something along the lines of a VBA function that will take a cell reference and "copy/paste" the formula virtually into an adjacent cell without actually pasting it?

Suggestions appreciated.

**bebo021999** · 09-14-2022, 01:05 AM

I came up with this algorithm: employ a helper range, to fill this range with autofill formula, then compare with formula range, then highlight cells those difference in formula.
But, it depends on what kink of used formula, fill down or fill accross
In your sample, it is filling down.
Suppose formula range is A3:B10, in which
A3 = $A$1*A2 and filled down
B3=$A$1+A3 and filled down

Now, make a test by manual changing formula in cells, i.e, A6 & B4

they will be highlighted with red.

PHP Code:


Option Explicit 
Sub checkformula() 
Dim cell As Range, checkRng As Range, tmp As Range 
Set checkRng = Range("A3:B10") ' change to actual range to check 
Set tmp = checkRng.Offset(, 1000) ' employ a helper range (the 1000th column to the right of checked range, make sure it is available) 
tmp.Rows(1).Formula = Range("A3:B3").Formula 'copy formula of first row 
tmp.FillDown 'fill formula to bottom 
For Each cell In checkRng 
    If cell.Interior.Color = vbRed Then cell.Interior.Color = xlNone 
    If cell.HasFormula Then 
        ' compare manual formula in checked range with filled formula in tmp range 
        If cell.Offset(, 10).Formula <> cell.Formula Then cell.Interior.Color = vbRed 
    End If 
Next 
tmp.ClearContents 
End Sub