VBA check range for matching rows

[Cooki]

Hi All

I have some data that has been input into a system and i have the export from that system as well.

I need to check if the data has been input correctly or not

Workbook1 has that data that has been input into the system

Workbook2 has the export.

There are multiple tabs on each, but each tab is called the same.

On workbook1 data starts from A2 and goes to K400

Workbook2 data starts from B2 and goes to L400

So basically i need to make sure that each set of row data has been into into workbook2 correctly and if not highlight red or something to show where the error is.

TIA

Rich

 

[mumps]

Do you want to compare each individual cell in WB1 to WB2? What is the name of the sheet in both workbooks?

 

[Cooki]

Do you want to compare each individual cell in WB1 to WB2? What is the name of the sheet in both workbooks?

So i need to compare the cells are a match and the other cells in that row

for example

Name	Age	M/F	Hight
Henry	66​	M	5ft 9

So i need to make sure that there is a exact match in the same order as row 2

need to find that match in WB2

Both work books have sheet name of "M3633 E17"

 

[mumps]

Let's assume that the example you posted is on row 2 of WB1 in the range A2:D2 and that same data is in row 2 of WB2 in the same range. Do you want to compare A2 of WB1 to A2 of WB2, then compare B2 of WB1 to B2 of WB2, B3 of WB1 to B3 of WB2 and B4 of WB1 to B4 of WB2? Also, will the rows of data to compare be on the same rows in each workbook, for example, if the data in your sample is in row 10 in WB1 will it be in row 10 in WB2 or can the data to compare be on different rows in each workbook?

 

[JLGWhiz]

This assumes both workbooks will be open at run time.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, i As Long
Set sh1 = Workbooks(1).Sheets(1) 'Edit workbook and sheet names
Set sh2 = Workbooks(2).Sheets(1) 'Edit workbook and sheet names
    With sh1
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            For i = 1 To 11
                If .Cells(c.Row, i).Value <> sh2.Cells(c.Row, i + 1).Value Then
                    .Cells(c.Row, i).Interior.Color = vbRed
                End If
            Next
        Next
    End With
End Sub

 

[Cooki]

Let's assume that the example you posted is on row 2 of WB1 in the range A2:D2 and that same data is in row 2 of WB2 in the same range. Do you want to compare A2 of WB1 to A2 of WB2, then compare B2 of WB1 to B2 of WB2, B3 of WB1 to B3 of WB2 and B4 of WB1 to B4 of WB2? Also, will the rows of data to compare be on the same rows in each workbook, for example, if the data in your sample is in row 10 in WB1 will it be in row 10 in WB2 or can the data to compare be on different rows in each workbook?

No the rows will not be in the same row in both workbooks

Need to compare the cells as a whole WB1 A2:K2 are in WB2

Needs to be like that as some of the columns have the same wording, so need to check all the cells in the row as a whole if that make sense

 

[Cooki]

This assumes both workbooks will be open at run time.

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, i As Long
Set sh1 = Workbooks(1).Sheets(1) 'Edit workbook and sheet names
Set sh2 = Workbooks(2).Sheets(1) 'Edit workbook and sheet names
    With sh1
        For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
            For i = 1 To 11
                If .Cells(c.Row, i).Value <> sh2.Cells(c.Row, i + 1).Value Then
                    .Cells(c.Row, i).Interior.Color = vbRed
                End If
            Next
        Next
    End With
End Sub

Just tried this get runtime error 424 Object required

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, i As Long
Set sh1 = master.M3633E17
Set sh2 = output.M3633E17
With sh1
For Each c In .Range("A2", .Cells(Rows.Count, 1).End(xlUp))
For i = 1 To 11
If .Cells(c.Row, i).Value <> sh2.Cells(c.Row, i + 1).Value Then
.Cells(c.Row, i).Interior.Color = vbRed
End If
Next
Next
End With
End Sub

But does this gone check the rest of the row as well up to K for instance?

 

[mumps]

Will the names column always be correct in both sheets or is there any other column that does not need to be checked? If so which column?

 

[Cooki]

Will the names column always be correct in both sheets or is there any other column that does not need to be checked? If so which column?

The columns will always be in the same order on both sheets.

So on WB1 (master) A:F

that will be checking on WB2 from B:G

 

[mumps]

Sorry but that doesn't answer my question. Please review Post #8. Since the data can be on different rows in each workbook, we need a way to make sure we are comparing the matching rows. In order to do that at least one column of data has to always be the same in both workbooks to act as unique identifier. So if the name in both workbooks will always be correct and doesn't need to checked, then the name can act as the unique identifier.