title: VBA - compare the cells in two columns with the cells in two other columns
body: <p>I have searched far and wide without finding a good answer for this issue.</p>
  
  <p>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).</p>
  
  <p>I want the script to start with A1 and B1 in sheet1, check if <em>both</em> 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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Here's how my code looks now:</p>
  
  <pre><code>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" &amp; i)) = True Then
      Exit For
  End If
  For j = 1 To 500000
  
  If IsEmpty(ws2.Range("A" &amp; j)) = True Then
         Exit For
         End If
  
  
  If ws.Range("A" &amp; i).Value = ws2.Range("A" &amp; j).Value Then
  
  If ws.Range("A" &amp; i).Offset(0, 1).Value = ws2.Range("A" &amp; j).Offset(0,   1).Value Then
  
                  ws.Range("A" &amp; i).Interior.Color = vbRed
              Else
  
                  ws.Range("A" &amp; i).Interior.Color = vbWhite
  
              End If
  
              Exit For
              End If
  
  Next j
  Next i
  MsgBox ("Finished ")
  End Sub
  </code></pre>
  
  <p>Thank you!</p>