Use index match to store and compare values between different sheets?

[brewitbold]

Hello,
Apologies if my post title is not the best summation of what I need. I am currently working with two different data sets that contain a unique identifier in column 8. This unique identifier is a concatenation of other fields within each row on each data set.

What I would like to do is perform an index-match in the second data set to find the corresponding value in data set one, and then determine if the value has changed between data set one and data set two. This could easily be accomplished without VBA if I only had 2 or 3 columns's worth of data to compare, but I need a solution that will scale up to to 30+ columns in future.

An example of the data from set one:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]ID NO[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Plan No.[/TD]
[TD]Plan Name[/TD]
[TD]Plan Status[/TD]
[TD]Start Date[/TD]
[TD]Unique ID[/TD]
[/TR]
[TR]
[TD]123456798[/TD]
[TD]J[/TD]
[TD]Test[/TD]
[TD]141068104[/TD]
[TD]A[/TD]
[TD]Approved[/TD]
[TD]2/6/2016[/TD]
[TD]123456798141068104A42403[/TD]
[/TR]
[TR]
[TD]454629[/TD]
[TD]C[/TD]
[TD]Jones[/TD]
[TD]549309401[/TD]
[TD]B[/TD]
[TD]Denied[/TD]
[TD]2/26/2016[/TD]
[TD]454629549309401B42426[/TD]
[/TR]
[TR]
[TD]344415[/TD]
[TD]Q[/TD]
[TD]Test[/TD]
[TD]694742136[/TD]
[TD]C[/TD]
[TD]Pending[/TD]
[TD]11/12/2016[/TD]
[TD]344415694742136C42675[/TD]
[/TR]
</tbody>[/TABLE]


An example of the data from set Two:
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]ID NO[/TD]
[TD]First Name[/TD]
[TD]Last Name[/TD]
[TD]Plan No.[/TD]
[TD]Plan Name[/TD]
[TD]Plan Status[/TD]
[TD]Start Date[/TD]
[TD]Unique ID[/TD]
[/TR]
[TR]
[TD]123456798[/TD]
[TD]J[/TD]
[TD]Test[/TD]
[TD]141068104[/TD]
[TD]A[/TD]
[TD]Approved[/TD]
[TD]2/3/2016[/TD]
[TD]123456798141068104A42403[/TD]
[/TR]
[TR]
[TD]454629[/TD]
[TD]C[/TD]
[TD]Jones[/TD]
[TD]549309401[/TD]
[TD]B[/TD]
[TD]Denied[/TD]
[TD]2/26/2016[/TD]
[TD]454629549309401B42426[/TD]
[/TR]
[TR]
[TD]344415[/TD]
[TD]Q[/TD]
[TD]Test[/TD]
[TD]694742136[/TD]
[TD]C[/TD]
[TD]Approved[/TD]
[TD]11/12/016[/TD]
[TD]344415694742136C42675[/TD]
[/TR]
</tbody>[/TABLE]


Here is the snippet of code I am considering (here only set to compare changes in the first 7 columns, this will eventually change). The idea is that in the above examples, cell(4,6) of data set two would be highlighted in red.

I receive an error which is commented below, and I also don't receive the intended result. Any help is appreciated!!

For i = 2 To lastrow2
    For j = 1 To 7
    'Index match the unique ID on data set two to find corresponding value in data set one for each column in data set
        test = Application.WorksheetFunction.Index(dataSetOne, Application.WorksheetFunction.Match(dataSetTwo.Cells(i, 8), dataSetTwo.Range("h:h"), 0), j)
    
    'store the value of data set two for each cell
        test2 = dataSetTwo.Cells(i, j)
    
    'compare the values and highlight if they do not match
        If test <> test2 Then

    'this line is where I receive a "Object required" error
        test2.Interior.Color = RGB(255, 199, 206)
        End If
    
    Next
    
Next

 

[Tetra201]

brewitbold:

Here is a tested-working code. I tried to retain as much of your style as possible
for you to better understand the changes made. I hope you know how to define
your dataSetOne and dataSetTwo.

For i = 2 To lastrow2
    For j = 1 To 7
    'Index match the unique ID on data set two to find corresponding value in data set one for each column in data set
        test = dataSetOne.Cells(Application.WorksheetFunction.Match(dataSetTwo.Cells(i, 8).Value, dataSetOne.Range("H:H"), 0), j).Value
    
    'store the value of data set two for each cell
        test2 = dataSetTwo.Cells(i, j).Value
    
    'compare the values and highlight if they do not match
        If test <> test2 Then
            dataSetTwo.Cells(i, j).Interior.Color = RGB(255, 199, 206)
        End If
    Next
Next