title: Compare two sheets using ID column
body: <p>I am looking to do a comparison of 2 sheets in a workbook in Excel 2013.  Due to the number of records VLOOKUP and other formulas have been slow so I thought I would try VB to see if this was a quicker solution.  </p>
  
  <p>What I would like to do is compare each record by ID and highlight and mismatches in red.  Due to the column names and position being different, I would also like to do the cell comparison on each record by specifying the column names to compare against.  Finally, I would like to total the mismatches for each column into a 3rd sheet.</p>
  
  <p>For example: </p>
  
  <pre><code>Sheet 1:  
  ID  Col1  Col2  Col3  Col4 
  1   1     1     1     1  
  2   2     2     2     1 
  3   3     3     3     3 
  4   4     4     4     4
  
  Sheet 2: 
  DBID  Col1   Col2  Field  Col3
  1     1      1      1      1
  2     2      2      2      2
  4     4      4      4      4 
  3     3      3      3      3
  </code></pre>
  
  <p>So in the above example I would only like to Col4 compared with Field column and only see the Field column for ID 2 highlighted as an error with ID records 3 and 4 ignored because they match and are just in different positions in the file.  </p>
  
  <p>I would normally sort on ID instead of picking out a particular ID, but conscious that there could be records missing which means the data would be misaligned.</p>
  
  <p>At the moment I have found this code which will highlight the mismatches in red, but matches cell by cell without taking into consideration that the columns and records might not be in the same order.</p>
  
  <pre><code>Sub RunCompare() 'Call the compareSheets routine Call compareSheets("Sheet1", "Sheet2") End Sub
  
    Sub compareSheets(shtBefore As String, shtAfter As String) Dim mycell As Range Dim mydiffs As Integer   'If current cell is not a date then proceed (else skip and go to next), then 'if not same as corresponding cell in sheet After, 'mark as yellow and repeat until entire range is used   For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange If Not IsDate(mycell) Then
      If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then
  
          mycell.Interior.Color = vbRed
          mydiffs = mydiffs + 1
  
      End If End If Next   'Display a message box stating the number of differences found MsgBox mydiffs &amp; " differences found", vbInformation ActiveWorkbook.Sheets(shtAfter).Select End Sub
  </code></pre>