VBA check range for matching rows

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
77
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
Do you want to compare each individual cell in WB1 to WB2? What is the name of the sheet in both workbooks?
 

Cooki

Board Regular
Joined
Jul 31, 2018
Messages
77
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


NameAgeM/FHight
Henry
66​
M5ft 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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

This assumes both workbooks will be open at run time.

VBA Code:
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

Board Regular
Joined
Jul 31, 2018
Messages
77
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

Board Regular
Joined
Jul 31, 2018
Messages
77

ADVERTISEMENT

This assumes both workbooks will be open at run time.

VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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

Board Regular
Joined
Jul 31, 2018
Messages
77
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,648
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.
 

Forum statistics

Threads
1,148,108
Messages
5,744,878
Members
423,908
Latest member
Getfour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top