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

#### Doozae

##### New Member
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:
``````<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``````

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### hiker95

##### Well-known Member
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

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

Borders-Copy-Paste

To test the above:
Test Here

sensitive data changed
mark the workbook for sharing

#### Ragnar1211

##### Well-known Member
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

##### New Member
I am using Excel 2007 on a Windows Computer.

#### hiker95

##### Well-known Member
Doozae,

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

Sample worksheets:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">100</td><td style=";">B1</td><td style=";">C1</td><td style=";">D1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">200</td><td style=";">B2</td><td style=";">C2</td><td style=";">D2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">222</td><td style=";">B3</td><td style=";">C3</td><td style=";">D3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">555</td><td style=";">B4</td><td style=";">C4</td><td style=";">D4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">100</td><td style=";">B1</td><td style=";">C1</td><td style=";">D1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">211</td><td style=";">B2</td><td style=";">C2</td><td style=";">D2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">222</td><td style=";">B3</td><td style=";">3C</td><td style=";">D3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">555</td><td style=";">4B</td><td style=";">C4</td><td style=";">4D</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

After the macro in worksheet Sheet2:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">100</td><td style=";">B1</td><td style=";">C1</td><td style=";">D1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">211</td><td style=";">B2</td><td style=";">C2</td><td style=";">D2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">222</td><td style=";">B3</td><td style="background-color: #FFFF00;;">3C</td><td style=";">D3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">555</td><td style="background-color: #FFFF00;;">4B</td><td style=";">C4</td><td style="background-color: #FFFF00;;">4D</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

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
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.

Code:
``````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

##### New Member
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

Replies
5
Views
39
Replies
3
Views
65
Replies
1
Views
68
Replies
20
Views
150
Replies
3
Views
24