I have searched far and wide without finding a good answer for this issue.
I have two lists with two columns in each. The lists contains dealer numbers (column A) and part numbers for the dealers (column B). The same value may be duplicate in each of the columns (each dealer has several part numbers and each part number may occur for several dealers).
I want the script to start with A1 and B1 in sheet1, check if both cells have a match in sheet2 - column A and column B and if so mark the equivalent cell in A1 as red, and then move to A2 + B2 to do the same comparison again. In other words, it should check row1 in sheet 1, compare it with each row in Sheet2 for a match, mark the A-cell in Sheet1 red if there is a match, and then move to the next row in Sheet1.
Here is where i have problems getting it right; I cannot seem to make the script flexible. My script does not seem to check both Cell A and B in Sheet1 and it does not check the full range in Sheet 2 for each loop.
In the next step I would also want the script to check if a third column in Sheet2 is higher than the respective cell in Sheet1, but I should be able to handle that once I get the basics going.
Here's how my code looks now:
Sub Comparestwocolumns()
Dim i As Long
Dim lastrow As Long
Dim ws As Worksheet
Set ws = Sheet1
Set ws2 = Sheet2
For i = 1 To 500000
If IsEmpty(ws.Range("A" & i)) = True Then
Exit For
End If
For j = 1 To 500000
If IsEmpty(ws2.Range("A" & j)) = True Then
Exit For
End If
If ws.Range("A" & i).Value = ws2.Range("A" & j).Value Then
If ws.Range("A" & i).Offset(0, 1).Value = ws2.Range("A" & j).Offset(0, 1).Value Then
ws.Range("A" & i).Interior.Color = vbRed
Else
ws.Range("A" & i).Interior.Color = vbWhite
End If
Exit For
End If
Next j
Next i
MsgBox ("Finished ")
End Sub
Thank you!