Compare Cells

[kwedde01]

Hello:

Iam trying to compare several columns on two sheets.

For example compare all the contents of column A,B,C,D on sheet1 with
the corresponding columns A,B,C,D on sheet2. Once it runs the
comparison, all cells which do not contain the same corresponding
values (ex sheet1.cell(a1) and sheet2.cell(a1)) will be colored yellow.
A Message box would also appear telling how many incorrect matches have
been found.

Please can someone help me
Thank you

Also could such a macro be used on more than two sheets simultaneously.

 

[William Benson]

Hi here ya go

Sub CompareSheets()
Dim Cell As Range

Sheet1.Range("A").Interior.ColorIndex = xlColorIndexNone
Sheet2.Range("A").Interior.ColorIndex = xlColorIndexNone
For Each Cell In Sheet1.Range("A").Cells
If Cell.Value <> Sheet2.Range(Cell.Address).Value Then
Cell.Interior.ColorIndex = 6
Sheet2.Range(Cell.Address).Interior.ColorIndex = 6
End If
Next Cell
End Sub


Best wishes,

Bill Benson, CPA/MBA
President
XLCREATIONS.COM
http://www.xlcreations.com

With you in the fight against "Automation Apathy" ©

 

[William Benson]

With tally and message box...

Sub CompareSheets()
Dim Cell As Range, Dim i As Long

Sheet1.Range("A").Interior.ColorIndex = xlColorIndexNone
Sheet2.Range("A").Interior.ColorIndex = xlColorIndexNone
For Each Cell In Sheet1.Range("A").Cells
If Cell.Value <> Sheet2.Range(Cell.Address).Value Then
Cell.Interior.ColorIndex = 6
i = i + 1
Sheet2.Range(Cell.Address).Interior.ColorIndex = 6
End If
Next Cell

If i > 0 Then MsgBox "There " & IIf(i > 1, "were ", "was ") & _
Format(i, "#,##0") & " mismatched " & IIf(i > 1, "values.", "value.")

End Sub

 

[kwedde01]

Hey thanks alot for the help, by any chance if i wanted it to search
every cell on the sheet1 and every cell on sheet2, without having to
specify a certain range, how would I do that?

 

[STEVE BELL]

For Each Cell In Sheet1.Range("A").Cells
change to this
For Each Cell In Sheet1.Range("A:IV").Cells

But you better be ready to take a long break - that's a lot of cells to
check

I would consider
For Each Cell In Sheet1.UsedRange

or build code to find the Last Cell and restrict your range to A1:LastCell

dim lr as long, lc as long

lr = Sheet1.Cells.SpecialCells(xlLastCell).Row
lc= Sheet1.Cells.SpecialCells(xlLastCell).Column

For each cell in range(cells(1,1),cells(lr,lc))