title: Comparing two excel files and putting the difference in a new sheet
body: <p>I am comparing two sheets that are like this:</p>
  
  <p><img src="https://i.stack.imgur.com/wTgT8.jpg" alt="excel sheets"></p>
  
  <p>One sheet is newer than the other and can have a row removed, added, or updated.</p>
  
  <p>My code to detect if a row is removed/added between the two is not working for me, here is what I have:</p>
  
  <pre><code>Private Sub CompareSpreadsheets()
      Dim xlApp1 As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
      Dim xlApp2 As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
      Dim xlApp3 As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
      Dim xlWorkBook1, xlWorkBook2, xlWorkBook3 As Excel.Workbook
      Dim xlWorkSheet1, xlWorkSheet2, xlWorkSheet3 As Excel.Worksheet
      Dim misValue As Object = System.Reflection.Missing.Value
      Dim xlRange1 As Excel.Range
      Dim maxRows As Short
      Dim qtyChange, mtlChange, newItem, delItem As Boolean
      Dim newRowCount As Short = 1
      Dim w3row As Short = 1
  
      'Check if Excel is installed on system
      If xlApp1 Is Nothing Then
          MessageBox.Show("Excel is missing/not properly installed on your system.", "ERROR")
          Return
      End If
  
      xlApp1.Workbooks.Open(path1)
      xlWorkBook1 = xlApp1.ActiveWorkbook
      xlWorkSheet1 = xlWorkBook1.ActiveSheet
  
      xlApp2.Workbooks.Open(path2)
      xlWorkBook2 = xlApp2.ActiveWorkbook
      xlWorkSheet2 = xlWorkBook2.ActiveSheet
  
      'Get maximum number of columns and rows from new spreadsheet
      maxRows = xlWorkSheet2.UsedRange.Rows.Count
  
      pbStatus.Minimum = 1
      pbStatus.Maximum = maxRows
  
      xlWorkBook3 = xlApp3.Workbooks.Add(misValue)
      xlWorkSheet3 = xlWorkBook3.Sheets("sheet1")
  
      qtyChange = False
      mtlChange = False
      newItem = False
      delItem = False
  
      Dim counter = 0 '/temp
  
      For i As Short = 1 To maxRows
          pbStatus.Value = i
  
          '[-- Green = Quantity change
          'Yellow = Material change
          'Blue = New item
          'Red = Deleted item --]
  
          If GetCellData(xlWorkSheet1, newRowCount, 3) = GetCellData(xlWorkSheet2, i, 3) And _
              GetCellData(xlWorkSheet1, newRowCount, 4) = GetCellData(xlWorkSheet2, i, 4) And _
              GetCellData(xlWorkSheet1, newRowCount, 5) = GetCellData(xlWorkSheet2, i, 5) And _
              GetCellData(xlWorkSheet1, newRowCount, 6) = GetCellData(xlWorkSheet2, i, 6) And _
              GetCellData(xlWorkSheet1, newRowCount, 7) = GetCellData(xlWorkSheet2, i, 7) And _
              GetCellData(xlWorkSheet1, newRowCount, 8) = GetCellData(xlWorkSheet2, i, 8) Then
              'DONE
              qtyChange = False
              mtlChange = False
              newItem = False
              delItem = False
              newRowCount += 1
          ElseIf GetCellData(xlWorkSheet1, newRowCount, 3) = GetCellData(xlWorkSheet2, i, 3) And _
              GetCellData(xlWorkSheet1, newRowCount, 4) = GetCellData(xlWorkSheet2, i, 4) And _
              GetCellData(xlWorkSheet1, newRowCount, 5) = GetCellData(xlWorkSheet2, i, 5) And _
              GetCellData(xlWorkSheet1, newRowCount, 6) &lt;&gt; GetCellData(xlWorkSheet2, i, 6) And _
              GetCellData(xlWorkSheet1, newRowCount, 7) = GetCellData(xlWorkSheet2, i, 7) And _
              GetCellData(xlWorkSheet1, newRowCount, 8) = GetCellData(xlWorkSheet2, i, 8) Then
              'DONE
              qtyChange = True
          ElseIf GetCellData(xlWorkSheet1, newRowCount, 1) = GetCellData(xlWorkSheet2, i, 1) And _
              GetCellData(xlWorkSheet1, newRowCount, 2) = GetCellData(xlWorkSheet2, i, 2) And _
              GetCellData(xlWorkSheet1, newRowCount, 3) = GetCellData(xlWorkSheet2, i, 3) And _
              GetCellData(xlWorkSheet1, newRowCount, 4) &lt;&gt; GetCellData(xlWorkSheet2, i, 4) And _
              GetCellData(xlWorkSheet1, newRowCount, 5) = GetCellData(xlWorkSheet2, i, 5) And _
              GetCellData(xlWorkSheet1, newRowCount, 6) = GetCellData(xlWorkSheet2, i, 6) And _
              GetCellData(xlWorkSheet1, newRowCount, 7) = GetCellData(xlWorkSheet2, i, 7) And _
              GetCellData(xlWorkSheet1, newRowCount, 8) = GetCellData(xlWorkSheet2, i, 8) Then
              'DONE
              mtlChange = True
          ElseIf GetCellData(xlWorkSheet1, newRowCount, 3) &lt;&gt; GetCellData(xlWorkSheet2, i, 3) And _
              GetCellData(xlWorkSheet1, newRowCount, 4) &lt;&gt; GetCellData(xlWorkSheet2, i, 4) Then
  
              If GetCellData(xlWorkSheet1, newRowCount + 1, 4) &lt;&gt; GetCellData(xlWorkSheet2, i, 4) Then
                  newItem = True
              ElseIf GetCellData(xlWorkSheet1, newRowCount, 4) &lt;&gt; GetCellData(xlWorkSheet2, i + 1, 4) Then
                  delItem = True
              End If
          End If
  
          'Procedure for each case
          If qtyChange Then
              xlWorkSheet3.Cells(w3row, 1) = GetCellData(xlWorkSheet2, i, 1)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 1), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Green
  
              xlWorkSheet3.Cells(w3row, 2) = GetCellData(xlWorkSheet2, i, 2)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 2), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Green
  
              xlWorkSheet3.Cells(w3row, 3) = GetCellData(xlWorkSheet2, i, 3)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 3), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Green
  
              xlWorkSheet3.Cells(w3row, 4) = GetCellData(xlWorkSheet2, i, 4)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 4), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Green
  
              xlWorkSheet3.Cells(w3row, 5) = GetCellData(xlWorkSheet2, i, 5)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 5), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Green
  
              xlWorkSheet3.Cells(w3row, 6) = GetCellData(xlWorkSheet2, i, 6)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 6), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Green
  
              xlWorkSheet3.Cells(w3row, 7) = GetCellData(xlWorkSheet2, i, 7)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 7), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Green
  
              xlWorkSheet3.Cells(w3row, 8) = GetCellData(xlWorkSheet2, i, 8)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 8), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Green
  
              newRowCount += 1
              w3row += 1
          ElseIf mtlChange Then
              xlWorkSheet3.Cells(w3row, 1) = GetCellData(xlWorkSheet2, i, 1)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 1), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Yellow
  
              xlWorkSheet3.Cells(w3row, 2) = GetCellData(xlWorkSheet2, i, 2)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 2), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Yellow
  
              xlWorkSheet3.Cells(w3row, 3) = GetCellData(xlWorkSheet2, i, 3)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 3), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Yellow
  
              xlWorkSheet3.Cells(w3row, 4) = GetCellData(xlWorkSheet2, i, 4)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 4), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Yellow
  
              xlWorkSheet3.Cells(w3row, 5) = GetCellData(xlWorkSheet2, i, 5)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 5), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Yellow
  
              xlWorkSheet3.Cells(w3row, 6) = GetCellData(xlWorkSheet2, i, 6)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 6), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Yellow
  
              xlWorkSheet3.Cells(w3row, 7) = GetCellData(xlWorkSheet2, i, 7)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 7), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Yellow
  
              xlWorkSheet3.Cells(w3row, 8) = GetCellData(xlWorkSheet2, i, 8)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 8), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Yellow
  
              newRowCount += 1
              w3row += 1
          ElseIf delItem Then
              xlWorkSheet3.Cells(w3row, 1) = GetCellData(xlWorkSheet1, newRowCount, 1)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 1), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Red
  
              xlWorkSheet3.Cells(w3row, 2) = GetCellData(xlWorkSheet1, newRowCount, 2)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 2), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Red
  
              xlWorkSheet3.Cells(w3row, 3) = GetCellData(xlWorkSheet1, newRowCount, 3)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 3), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Red
  
              xlWorkSheet3.Cells(w3row, 4) = GetCellData(xlWorkSheet1, newRowCount, 4)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 4), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Red
  
              xlWorkSheet3.Cells(w3row, 5) = GetCellData(xlWorkSheet1, newRowCount, 5)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 5), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Red
  
              xlWorkSheet3.Cells(w3row, 6) = GetCellData(xlWorkSheet1, newRowCount, 6)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 6), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Red
  
              xlWorkSheet3.Cells(w3row, 7) = GetCellData(xlWorkSheet1, newRowCount, 7)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 7), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Red
  
              xlWorkSheet3.Cells(w3row, 8) = GetCellData(xlWorkSheet1, newRowCount, 8)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 8), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Red
  
              newRowCount += 1
              i -= 1
              w3row += 1
          ElseIf newItem Then
              xlWorkSheet3.Cells(w3row, 1) = GetCellData(xlWorkSheet2, i, 1)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 1), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Blue
  
              xlWorkSheet3.Cells(w3row, 2) = GetCellData(xlWorkSheet2, i, 2)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 2), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Blue
  
              xlWorkSheet3.Cells(w3row, 3) = GetCellData(xlWorkSheet2, i, 3)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 3), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Blue
  
              xlWorkSheet3.Cells(w3row, 4) = GetCellData(xlWorkSheet2, i, 4)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 4), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Blue
  
              xlWorkSheet3.Cells(w3row, 5) = GetCellData(xlWorkSheet2, i, 5)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 5), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Blue
  
              xlWorkSheet3.Cells(w3row, 6) = GetCellData(xlWorkSheet2, i, 6)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 6), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Blue
  
              xlWorkSheet3.Cells(w3row, 7) = GetCellData(xlWorkSheet2, i, 7)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 7), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Blue
  
              xlWorkSheet3.Cells(w3row, 8) = GetCellData(xlWorkSheet2, i, 8)
              xlRange1 = CType(xlWorkSheet3.Cells(w3row, 8), Excel.Range)
              xlRange1.Font.Color = System.Drawing.Color.Blue
  
              w3row += 1
          End If
  
          qtyChange = False
          mtlChange = False
          newItem = False
          delItem = False
      Next
  
      'Save new spreadsheet [TEMP NAME]
      xlWorkBook3.SaveAs("C:\Users\Windows\Documents\Visual Studio 2013\Projects\test.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, _
                  Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
      xlWorkBook3.Close(True, misValue, misValue)
      xlApp3.Quit()
  
      '----------------------------------
      'End of process, cleaning up CPU---
      xlWorkBook1.Close(True, misValue, misValue)
      xlApp1.Quit()
  
      xlWorkBook2.Close(True, misValue, misValue)
      xlApp2.Quit()
  
      ReleaseObject(xlWorkSheet1)
      ReleaseObject(xlWorkBook1)
      ReleaseObject(xlApp1)
  
      ReleaseObject(xlWorkSheet2)
      ReleaseObject(xlWorkBook2)
      ReleaseObject(xlApp2)
  
      ReleaseObject(xlWorkSheet3)
      ReleaseObject(xlWorkBook3)
      ReleaseObject(xlApp3)
  
      cmdFormat.Text = "Process complete. Compare new files?"
  End Sub
  </code></pre>
  
  <p>EDIT: What's not working? It will skip identical rows fine and also detect the cell changes (I have column 4 and 6 set which return them as green/yellow) but once my code finds a deleted entry in the old worksheet/new entry in the new sheet it messes up.</p>
  
  <p>Here is where I believe it is messing up:</p>
  
  <pre><code>ElseIf GetCellData(xlWorkSheet1, newRowCount, 3) &lt;&gt; GetCellData(xlWorkSheet2, i, 3) And _
          GetCellData(xlWorkSheet1, newRowCount, 4) &lt;&gt; GetCellData(xlWorkSheet2, i, 4) Then
  
          If GetCellData(xlWorkSheet1, newRowCount + 1, 4) &lt;&gt; GetCellData(xlWorkSheet2, i, 4) Then
              newItem = True
          ElseIf GetCellData(xlWorkSheet1, newRowCount, 4) &lt;&gt; GetCellData(xlWorkSheet2, i + 1, 4) Then
              delItem = True
          End If
      End If
  </code></pre>
  
  <p>If anyone can see the issue please let me know. Or if you have another solution to this. Thanks!</p>