title: How to go through a list on two different worksheets comparing values using VBA
body: <p>I am trying to match 2 columns in one worksheet with 2 (or potentially more) columns in another worksheet. </p>
  
  <p>I have posted a sample of my worksheet data which hopefully gives a good description of what I have been trying to do for the past week. I think I am on the right track but I don't know how to properly reference between the two worksheets. </p>
  
  <p>What I would like to do is look at Location 1 column, then see if that location references with my RefSheet in either location 1 or location 2.</p>
  
  <p>If it does, I want to see if Location 2 then matches up in the somewhere in the same row on the RefSheet. If there is a match, I want to highlight the cell/cells yellow and give the ID number from RefSheet.</p>
  
  <p>If there is no match I want to either highlight it red or no highlight.</p>
  
  <pre><code>Sheet All
  A       B               C               D
  ID  Location 1  Location 2  Given ID
  1   West    North   
  2   North   South   
  3   South   East    
  4   East    West    
  5   East    East    
  6   South   West
  
  
  
  
  Sheet RefSheet
  A       B               C   
  ID  Location 1     Location 2
  1   West            North
  2   West            East
  3   South           East
  4   South           North
  
  
  
  What it should look like on the original Worksheet
  A               B               C           D
  ID          Location 1  Location 2      Given ID
  1(Yellow)   West            North           1
  2(Yellow)   North           South           4
  3(Yellow)   South           East            3
  4(Yellow)   East            West            2
  5(Red)      East            East    
  6(Red)      South           West    
  </code></pre>
  
  <p>Here is my terrible Code</p>
  
  <pre><code>    Sub roadfinder()
  
      Dim lngLast As Long
      Dim lngCounter As Long
      Dim rCell As Range
      Dim lCnt As Long
      Dim nextIntersection
      Dim RefSheet As Worksheet
      Dim list As Worksheet
  
      Set intersections = ThisWorkbook.Sheets("RefSheet")
      Set crashes = ThisWorkbook.Sheets("All")
  
  
      Application.ScreenUpdating = False
      lngLast = Cells(Rows.Count, "B").End(xlUp).Row
      For lngCounter = 2 To lngLast
          With Cells(lngCounter, "B")
  
              For Each rCell In RefSheet.Range("B1", RefSheet.Cells(RefSheet.Rows.Count, 1)).Cells
              lCnt = lCnt + 1
  
                  'I wasn't sure what to put as a reference to
                  If .Value = "" Then
                      .Interior.ColorIndex = 6
  
                  End If
  
              Next rCell
          End With
      Next lngCounter
  
      Application.ScreenUpdating = True
  
  End Sub 
  </code></pre>