Compare two sheets

[austenr]

What do I have to change to make it print the whole line on the recon report not just the cell(s) that are different? I played around with it a couple of ways but had no luck. I know you have to change the .Cells to .Row I think. Thanks guys. You rock!!

Public Sub ReconReport()
    Dim rngCell As Range
For Each rngCell In Worksheets("Sheet1").UsedRange
        If Not rngCell = Worksheets("Sheet2").Cells(rngCell.Row, rngCell.Column) Then _
            Let Worksheets("Sheet3").Cells(rngCell.Row, rngCell.Column) = rngCell
    Next
End Sub

[austenr]

My other post did not get any responses so I am trying again. I want to modify this code to select and write the entire row on Sheet3 instead of just the cells that are changed. Thanks in advance.

Sub UniqueList()
'Build a new unique list from two other lists
'Works by checking list 1 for missing values in list 2
'Sheet1 is the master list of names.
'Sheet2 is the raw data
'Sheet3 gets the names that are in the raw data but not on the master list.
Set MyFunction = Application.WorksheetFunction
Set MyRange1= Sheets("Sheet1").Range("A2:A500")
Set MyRange2 = Sheets("Sheet2").Range("A2:A500")
Set MyResults = Sheets("Sheet3").Range("A2")
?Loop for list values
Sheets("Sheet1").Select
For Each cell In My Range2
On Error GoTo myFin
Sheets("Sheet2").Select
If MyFunction.CountIf(MyRange1, cell.Value) = 0 Then
Sheets("Sheet3").Select
Set MyResults = MyResults.Offset(r, 0)
MyResults.Value = cell.Value
r = r + 1
End If
Next Cell
myFin:
Sheets("Sheet3").Select
Sheets("Sheet3").Range("A1").Select
End Sub

[Zack Barresse]

Hi Austen, I don't think you need the Let ...

Public Sub ReconReport()
Dim rngCell As Range
For Each rngCell In Worksheets("Sheet1").UsedRange
If Not rngCell.Value = Worksheets("Sheet2").Cells(rngCell.Row, rngCell.Column).Value Then
Worksheets("Sheet3").Cells(rngCell.Row, rngCell.Column) = rngCell.Value
End If
Next
End Sub

Btw, I merged your two threads. There's no need to keep making posts on the same thread. Just give it time.

[austenr]

will either of these print the entire row if it finds a discrepancy? Or could they be modified to do so? Thanks

[Zack Barresse]

Can you post a zipped example spreadsheet?

[austenr]

Here is the example file. As you can see there are rows with no change, rows with one or two differences and one record that totally does not match the other. I want to print the entire row of Sheet1 if there are any changes in any cell or if there is a new row on Sheet1 that does not appear on Sheet2. I wanted to write the rows to Sheet3. Hope this helps explain it better.

[Zack Barresse]

So in your example file, which rows would be copied to sheet 3? You do want the entire row copied over if there is a discrepancy, right? And then you want to print out that sheet (sheet3)?

[austenr]

Right. Also if there is one (an entire row on the raw data that does not match any thing on the master). Right now it would write the raw data because it has no corresponding data that matches. But if the raw data and master differ even by one cell I would want to write the entire row. HTH

[Zack Barresse]

Okay, here is a routine. I am a little confused as to which sheet is the 'Master' and which is the 'raw data' though..

Option Explicit

Sub CheckValuesAndMoveEm()
    Dim rngCel As Range, rngChk As Range, tmpCel As Range, _
        tmpRng As Range, myRow As Long, myCol As Long, cel As Range
    Set rngChk = Sheets("Sheet1").Range("A1", Sheets("Sheet1"). _
        Range("A65536").End(xlUp))
    For Each rngCel In rngChk
        With Sheets("Sheet2")
            Set tmpCel = .Range("A:A").Find(rngCel.Value)
            If Not tmpCel Is Nothing Then
                Set tmpRng = Range(tmpCel, .Cells(tmpCel.Row, 256). _
                    End(xlToLeft))
                For Each cel In tmpRng
                    If cel.Value <> Sheets("Sheet1").Cells(cel.Row, _
                        cel.Column).Value Then
                        rngCel.EntireRow.Copy Sheets("Sheet3"). _
                            Range("A65536").End(xlUp).Offset(1)
                        GoTo nextCellPlease
                    End If
                Next cel
            Else
                '** NOT FOUND **
            End If
        End With
nextCellPlease:
    Next rngCel
End Sub

This will search sheet2 for differences in sheet1, and put them all in sheet3. Is that what you wanted or do I have it backwards?

[austenr]

hey thanks..Sheet1 is the raw data Sheet2 is the master...

[austenr]

Hey that works copying the entire row, however, if you look at the example file I am attaching there is an extra row that does not get written to Sheet3. I have been looking at it and tried a few things testing wise but can't figure out why it does not write the row. Any ideas?

[Zack Barresse]

Because I didn't put anything in there if not found. You see the commented code in the Else statement

'** NOT FOUND **

Replace that with another statement like this ...

rngCel.EntireRow.Copy Sheets("Sheet3"). _ 
                    Range("A65536").End(xlUp).Offset(1)

That should get you the rows not found in the second sheet.

[austenr]

Works great. Thanks. Someone please take a look at what I tried to modify this to highlight different colors. Compiles o.k. but I get an error that the "Object does not support this" with respect to the highlighting I am trying to apply. Thanks.