VBA compare rows in two different sheets

[Lorlai]

Hello all,

I have two worksheets that need comparing. Sometimes a whole row of data may be added/deleted from one of the worksheets. I need to find a way to compare this to another worksheet. For instance, if one worksheet looks like:

John Doe 12345
Sarah Smith 67890
John Smith 23456
Martha Jones 98765

And the next Sheet looks like:
John Doe 12345
John Smith 23456
Martha Jones 98765
Donna Noble 018234

How can I create the highlighted change indicating that the row was different in sheet 2?

Thanks for any help that may be provided!

 

[hiker95]

Lorlai,


I assume that your data is only in columns A and B. I am using column C as a work area.

And, you only want to see if a row in Sheet2 is not in Sheet1, and then make the row in Sheet2 bold.


Sample worksheets:

Excel Workbook
	A	B
1	John Doe	12345
2	Sarah Smith	67890
3	John Smith	23456
4	Martha Jones	98765
5
Sheet1

Excel Workbook
	A	B
1	John Doe	12345
2	John Smith	23456
3	Martha Jones	98765
4	Donna Noble	018234
5
Sheet2

After the macro in Sheet2:

Excel Workbook
	A	B
1	John Doe	12345
2	John Smith	23456
3	Martha Jones	98765
4	Donna Noble	018234
5
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub Compare2Sheets()
' hiker95, 07/13/2011
' http://www.mrexcel.com/forum/showthread.php?t=564090
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, LR As Long, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
LR = w1.Cells(Rows.Count, 1).End(xlUp).Row
With w1.Range("C1:C" & LR)
  .FormulaR1C1 = "=RC[-2]&RC[-1]"
  .Value = .Value
End With
LR = w2.Cells(Rows.Count, 1).End(xlUp).Row
With w2.Range("C1:C" & LR)
  .FormulaR1C1 = "=RC[-2]&RC[-1]"
  .Value = .Value
End With
For Each c In w2.Range("C1", w2.Range("C" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w1.Columns(3), 0)
  On Error GoTo 0
  If FR = 0 Then
    c.Offset(, -2).Resize(, 2).Font.Bold = True
  End If
Next c
w1.Columns(3).ClearContents
w2.Columns(3).ClearContents
Application.ScreenUpdating = True
End Sub

Then run the Compare2Sheets macro.

 

[John Davis]

Hello all,

I have two worksheets that need comparing. Sometimes a whole row of data may be added/deleted from one of the worksheets. I need to find a way to compare this to another worksheet. For instance, if one worksheet looks like:

John Doe 12345
Sarah Smith 67890
John Smith 23456
Martha Jones 98765

And the next Sheet looks like:
John Doe 12345
John Smith 23456
Martha Jones 98765
Donna Noble 018234

How can I create the highlighted change indicating that the row was different in sheet 2?

Thanks for any help that may be provided!

FWIW:

Sub Lorlai()
Dim lr As Long
Dim x As Range
Dim xr As Range

Application.ScreenUpdating = False

lr = Cells(Rows.Count, 1).End(3).Row

Sheets("Sheet2").Columns("C:C").Insert Shift:=xlToRight

Set xr = Sheets("Sheet2").Range("C2:C" & lr)

With xr
        
        .Formula = "=IF(ISNA(VLOOKUP(A2,Sheet1!$A$2:$B$100,1,false)),""Bold"","""")"
        .Value = .Value
    
End With

For Each x In xr

    If x.Value = "Bold" Then x.EntireRow.Font.Bold = True
    
Next

Sheets("Sheet2").Columns("C:C").Delete Shift:=xlToLeft



Application.ScreenUpdating = True

End Sub