VB code for comparision of two rows based on a key column

Question

I have a testing requirement which has a lot of rows of data in two tabs.

1. One row from Sheet1 hast to be brought to Sheet2 based on Employee Name and inserted below the row with the matching Employee Name. For example :

   Employee Name     Salary    Rent      Loan
    Adler, Jorge     $10,000   $2,000  $15,000 
    Adler, Jorge     $10,000   $2,000  $14,000 
    Byrne, Aleena    $12,000   $1,500  $0 
    Byrne, Aleena    $11,000   $1,500  $0 
   

Here the Second Row has to be brought in and placed from another tab,and the 4th row,6th row etc...

2.Then the Two rows have to be compared at a time based on a row - Employee Name. There are different columns such as Salary, Rent, Loan etc...If the Salary of a Employee differs in the two rows both the salary cells should be highlighted.

Sample Data :

Employee Name      Salary       Rent      Loan
Adler, Jorge       $10,000   $2,000     $15,000
Adler, Jorge       $10,000   $2,000     $14,000
Byrne, Aleena      $12,000   $1,500     $0
Byrne, Aleena      $11,000   $1,500     $0 
Bogie, Douglas     $5,000        $1,200     $1,795
Bogie, Douglas     $5,000       $1,225  $1,795

Here both the Loan cells for Adler,Jorge and both Salary cells for Byrne,Aleena and both the Rent cells for Bogie,Douglas must be highlighted.

Answer 1

Here's one more possible solution:

If you always know for certain that the two rows will be right next to each other, you can use this, which eliminates the inner loop.

Public Sub HighlightDifferent()
Dim rngMain As Range, rngCompare As Range, rngAfterMain As Range, rng As Range
With ActiveSheet.UsedRange
  For Each rngMain In .Rows
    If rngMain.Columns(1) = rngMain.Offset(1, 0).Columns(1) Then
      For Each rng In rngMain.Columns
        If rng <> rngMain.Offset(1, 0).Columns(rng.Column) Then
          rng.Interior.Color = vbYellow
          rngMain.Offset(1, 0).Columns(rng.Column).Interior.Color = vbYellow
        End If
      Next
    End If
  Next
End With
End Sub

Here is the Merge and Sort Functionality:

Public Sub MergeAndSort()

Dim wks As Worksheet, rng As Range
Set wks = ThisWorkbook.Worksheets.Add
Worksheets("Sheet1").UsedRange.Copy wks.Range("A1")

With Worksheets("Sheet2").UsedRange
  Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
End With

With wks.UsedRange
  rng.Copy .Offset(.Rows.Count, 0).Resize(1, 1)
End With

wks.UsedRange.Sort key1:=wks.Range("A2"), order1:=xlAscending, Header:=xlYes

End Sub

Answer 2

Try this:

If you had two columns next to each other and you wanted to highlight which rows had different values, you could use this:

Public Sub HighlightDifferent()
Dim rng As Range
For Each rng In ActiveSheet.UsedRange.Rows
  If rng.Columns(1) <> rng.Columns(2) Then rng.Interior.Color = vbYellow
Next
End Sub

Try using that first in a test workbook, and then adapt the code to work with your layout