title: VBA Compare rows/columns in Excel
body: <p>I'm working on a VBA code that will allow me to see changes in trade positions day by day. I’m fairly new to VBA programming and would like to get some feedback on the code I have written (and copied from various posts here).</p>
  
  <p>I have 3 sheets, one with today's trades (sheet1), one with yesterday's trades (sheet2) and one sheet that are supposed to highlight changes from "day n" to "day n-1" (sheet3). In column "A" I have trade ID that are unique for each trade (ID1, ID2 etc.), from column "B" to "AA", I have data such as dates, values, text etc.</p>
  
  <p>In the "changes" sheets (sheet3), I would like to have a comment in column "A" that says if the trade is “New”, “Deleted/matured” or “Changed”. If one trade is changed, I would like to see which cells that are changed by showing before and after values.</p>
  
  <p>My main concerns are errors in my code and performance, e.g. sometimes I will have more than 500 rows x 30 columns in the spreadsheets.</p>
  
  <p>When I download data from my trade system to excel, I will sometimes have cells that have no content, and I would like my code to take this into account. I don't think my current code does this in a good way.</p>
  
  <p>Any suggestions/feedback would be appreciated!</p>
  
  
  
  <pre><code>Sub CompData()
  
      Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
      Dim ws1LRow As Long, ws2LRow As Long
      Dim i As Long, j As Long
      Dim ws1LCol As Long, ws2LCol As Long
      Dim Cell1 As Range, Cell2 As Range
      Dim SearchString As String
      Dim MatchFound As Boolean
      Dim n As Integer
      Dim NewDeal As String, MatDeal As String, ChangedDeal As String
  
      NewDeal = "New deal"
      MatDeal = "Matured/deleted deal"
      ChangedDeal = "Changed deal"
  
  
      Set ws1 = Sheets("sheet1")
      With ws1
          ws1LRow = .Range("A" &amp; .Rows.Count).End(xlUp).Row
          ws1LCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
      End With
  
      Set ws2 = Sheets("sheet2")
      With ws2
          ws2LRow = .Range("A" &amp; .Rows.Count).End(xlUp).Row
          ws2LCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
      End With
  
      Set ws3 = Sheets("sheet3")
      With ws3
          Cells.Clear
      End With
  
      n = 1
  
      For i = 1 To ws1LRow
  
          SearchString = ws1.Range("A" &amp; i).Value
  
          Set Cell1 = ws2.Columns(1).Find(What:=SearchString, LookIn:=xlValues, _
                  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False)
  
  
  
  
          If Not Cell1 Is Nothing Then
              Set Cell2 = Cell1
              MatchFound = True
  
              For j = 1 To ws1LCol
                  If ws1.Cells(i, j).Value &lt;&gt; ws2.Cells(Cell1.Row, j).Value Then
                      MatchFound = False
                      Exit For
                  End If
              Next
  
              If MatchFound = False Then
                  ws3.Cells(n, 2).Value = ws1.Cells(i, 1).Value
                  ws3.Cells(n, j + 1).Value = ws1.Cells(i, j).Value
                  ws3.Cells(n, ws2LCol + 2).Value = ws2.Cells(i, 1).Value
                  ws3.Cells(n, ws2LCol + j + 1).Value = ws2.Cells(i, j).Value
                  ws3.Cells(n, 1).Value = ChangedDeal
                  n = n + 1
              End If
  
          Else:
              ws3.Cells(n, 2).Value = ws1.Cells(i, 1).Value
              ws3.Cells(n, 1).Value = NewDeal
              n = n + 1
          End If
      Next
  
      ws2.Select
      For i = 1 To ws2LRow
          SearchString = ws2.Range("A" &amp; i).Value
  
          Set Cell2 = ws1.Columns(1).Find(What:=SearchString, LookIn:=xlValues, _
                  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False)
          If Cell2 Is Nothing Then
              ws3.Cells(n, 2).Value = ws2.Cells(i, 1).Value
              ws3.Cells(n, 1).Value = MatDeal
              n = n + 1
          End If
      Next
  
      ws3.Select
  
  End Sub
  </code></pre>