compare sheets (values and formulas), alert it like spellcheckers

[coco]

I have 20 sheets (like templates) and ALL of them have different information
ONLY for rows 2, 3, 4 and 5. The remaining cells of these sheets have
information (formulas and/or values) that will match each other.

The main-sheet, the one with the right Formulas and Values, will be the
current sheet that I will start running this macro.

I am looking for something like a spellchecker, but in this case will be
like a “Formulas and Values checker between similar Sheetsâ€.

For example: if I am in sheet3 and run this macro, it will compare formulas
and values in each CELLS, (but not on rows 2, 3, 4 and 5), between sheet3 and
the rest of sheets.

Moreover, It will prompt me for the first occurrence that this macro find
different between sheet3 and sheetâ€n†cells. This prompt will let me decide
to update the cell in sheet3, sheetâ€nâ€, to Ignore the prompt or cancel the
macro.

Any idea how to implement this macro?

Thanks

Coco.

 

[Tom Ogilvy]

Assume the UsedRange starts in A1

Sub aa()
Dim sh As Worksheet
Dim rng As Range, rng1 As Range
Dim rng2 As Range, cell As Range
Dim rng3 As Range, rng4 As Range
Dim ans As Long, msg As String
Set sh = ActiveSheet
Set rng = sh.UsedRange
Set rng1 = sh.Range(sh.Range("A5"), rng(rng.Count))
Set rng2 = Union(rng.Rows(1), rng1)
For Each sh1 In ActiveWorkbook.Worksheets
If sh1.Name <> sh.Name Then
Set rng3 = sh1.Range(rng2.Address)
For Each cell In rng3
Set rng4 = sh.Cells(cell.Row, cell.Column)
If cell.Formula <> rng4.Formula Then
msg = "Base Formula: " & rng4.Formula & vbNewLine & _
sh1.Name & " Formula: " & cell.Formula & vbNewLine & _
vbNewLine & "Make the same? "
ans = MsgBox(msg, vbYesNoCancel, "Difference found")
Select Case ans
Case vbYes
cell.Formula = rng4.Formula
Case vbCancel
Exit Sub
End Select
End If
Next
End If
Next
End Sub

 

[coco]

Tom,
Thank you for your response.

One more question
What about if I want to add a restriction: to EXCLUDE for the range to
search, "values or formulas" from all cells located in columns A and C.

Do I have to change the line that says?
"Set rng2 = Union(rng.Rows(1), rng1) "


Thanks

Coco

 

[coco]

Tom,
I got it.
Updated the "union" function: adding a 3rd column, a the column B.
Thanks

Coco