Compare content of rows in two different sheets if content of the first cell is true

[Doozae]

Hi Folks,
What I am trying to do is coding a macro that compares the rows in sheet1 and sheet2 and highlights the differences. You can see the macro for that below.


My problem is that in case a row or more is added or deleted in the first sheet, all other rows shift up/down which results in a lot of cells being marked in the second sheet.


Since I have an identifier in column A I try to implement an extra line which first looks for the same identifier in sheet 1 and once found, compares the rows of sheet 1 and sheet two, which have the same identifier and then marks potential differences.

Since all my ideas have failed so far, or made the file very slow, I hope you can help me out. Thank you very much!

<code>Sub comparing() 
Dim sh1 As Worksheet, sh2 As Worksheet 
Dim rCount As Long, cCount As Long 
Set sh1 = Worksheets(ActiveWorkbook.Worksheets.Count() - 1) 
Set sh2 = Worksheets(ActiveWorkbook.Worksheets.Count) 
rCount = sh1.Cells(Rows.Count, 1).End(xlUp).Row 
cCount = sh1.Cells(1, Columns.Count).End(xlToLeft).Column 
Dim r As Long, c As Integer For r = 1 To rCount For c = 1 To cCount         
If sh1.Cells(r, c) <> sh2.Cells(r, c) Then            
sh2.Cells(r, c).Interior.ColorIndex = 6         
End If     
Next c 
Next r 
</code>End Sub

 

[hiker95]

Doozae,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Can you post a screenshot of the two actual raw data worksheets?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Ragnar1211]

You can put the reference to the cell in a named range, then collect the reference in VBA, no need to worry about additional rows.

 

[Doozae]

I am using Excel 2007 on a Windows Computer.

 

[hiker95]

Doozae,

I was hoping for some screenshots to better understand what we are working with, and, what the results should look like.

Sample worksheets:

Excel 2007
	A	B	C	D
1	100	B1	C1	D1
2	200	B2	C2	D2
3	222	B3	C3	D3
4	555	B4	C4	D4
5
Sheet1

Excel 2007
	A	B	C	D
1	100	B1	C1	D1
2	211	B2	C2	D2
3	222	B3	3C	D3
4	555	4B	C4	4D
5
Sheet2

After the macro in worksheet Sheet2:

Excel 2007
	A	B	C	D
1	100	B1	C1	D1
2	211	B2	C2	D2
3	222	B3	3C	D3
4	555	4B	C4	4D
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
2. Open your NEW 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
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub CompareSheets()
' hiker95, 10/08/2014, ME810458
Dim w1 As Worksheet, w2 As Worksheet
Dim lr As Long, lc As Long, c As Long
Dim d As Range, a As Range
Application.ScreenUpdating = False
Set w1 = Worksheets(ActiveWorkbook.Worksheets.Count() - 1)
Set w2 = Worksheets(ActiveWorkbook.Worksheets.Count)
With w1
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For Each d In w1.Range("A1", w1.Range("A" & Rows.Count).End(xlUp))
    Set a = w2.Columns(1).Find(d.Value, LookAt:=xlWhole)
    If Not a Is Nothing Then
      For c = 2 To lc
        If w1.Cells(d.Row, c) <> w2.Cells(a.Row, c) Then
          w2.Cells(a.Row, c).Interior.ColorIndex = 6
        End If
      Next c
      Set a = Nothing
    End If
  Next d
End With
w2.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the CompareSheets macro.

 

[Doozae]

Thank you for the replies so far. The problem basically is:

Sheet1:

Ident	Value1	Value2	Value3
AA	11	111	1111
BB	22	222	2222
CC	33	333	3333

<tbody>
</tbody>

Sheet2

Ident	Value1	Value2	Value3
AA	11	111	1111
BB	22	222	2222
CC	33	333	333333

<tbody>
</tbody>

Right now, the last cell would be marked, because something changed.
However, when I add a row in Sheet1, all values in CC would be marked as former CC is now BB1 and has different values:

Sheet1A

Ident	Value1	Value2	Value3
AA	11	111	1111
BB	22	222	222
BB1	999	999	999
CC	33	333	333

<tbody>
</tbody>

So what I am looking for is that the macro first checks the Ident in column A and then compares the two rows, with the same ident.
I am unable to test the code posted above right now, but I think it should work. Thank you for that great support