title: Compare two ranges on different sheets and if the values match copy range
body: <p>I have two sheets (Current Month and Previous Month) with approx 4000 different account numbers and details in each row. The account numbers are compared and if an account number in the Current Month sheet is not in the Previous Months sheet it is highlighted in yellow. THIS PART WORKS FOR ME IN THE CODE BELOW. </p>
  
  <p>The part that I cannot get to work. If the account numbers match, copy columns H to N for that matching account number from the Previous Month sheet to columns H to N in the current months sheet. I'm getting Runtime error 9 - subscript out of range, when I'm trying to define the LastRow variable but I'm also not sure if the loop below that error will work either. </p>
  
  <p>Here is the code;</p>
  
  <pre><code>Sub RangeCompare()
  '   The range Previous Month is the baseline so if the range Current Month has a value that is not in the Previous Month it is highlighted
  '   If the Previous Month range has a value that is in Current Month then copy Previous Months columns H to N to Current Month columns H to N
  '   ACCOUNT NUMBERS SELECTED MUST BE IN COLUMN A, IF NOT THE COPY/PASTE WILL NOT WORK!
  
  '   Warning to the end user that column A must contain the Account Numbers or this will cause the copy part of the macro to fail
      MsgBox "!! Column A must contain the Account Numbers !!"
  
      Dim PrevMonth As Range, CurMonth As Range, c As Range, TempVal As Range
      Dim CurSheet As String, PrevSheet As String
      Dim LastRow1 As Long
  
      Set PrevMonth = Application.InputBox("Select appropriate sheet and all accounts:", Title:="Select Previous Months Accounts", Type:=8)
  '    next variable needed so VBA knows what the Previous worksheet name is, this is needed for the copying section of the macro
      PrevSheet = ActiveSheet.Name
      If PrevMonth Is Nothing Then
          MsgBox "No range selected. Ending program..."
          Exit Sub
      End If
  
      Set CurMonth = Application.InputBox("Select appropriate sheet and all accounts:", Title:="Select Current Months Accounts", Type:=8)
      ' next variable needed so VBA knows what the Current worksheet name is, this is needed for the copying section of the macro
      CurSheet = ActiveSheet.Name
      LastRow2 = Selection.Rows.Count
      If CurMonth Is Nothing Then
          MsgBox "No range selected. Ending program..."
          Exit Sub
      End If
  
  
  '   Highlight cells not in PrevMonth to yellow Colorindex = 6
  '   This loop works :-)
      For Each c In CurMonth.Cells
          If Application.WorksheetFunction.CountIf(PrevMonth, c.Value) = 0 Then
              c.Interior.ColorIndex = 6
          End If
      Next c
  
  '    Copy columns H:N from previous month sheet to H:N in current months sheet if Account numbers match
  
     LastRow1 = Sheets("PrevSheet").Range("PrevMonth").Rows.Count
  '  The above gives runtime error 9 - subscript out of range
  '  I don't know if the loop below will work as I can't define LastRow1 due to the error above.
      For sRow = 2 To LastRow1
          TempVal = Sheets(PrevSheet).Range(sRow, "H").Range(sRow, "N")
               If Sheets("CurSheet").Cells(sRow, 1).Text = Sheets("PrevSheet").Cells(sRow, 1).Text Then
                      Sheets(CurSheet).Range(sRow, "H").Range(sRow, "N") = TempVal
               End If
      Next sRow
  
  End Sub
  </code></pre>