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

Doozae

New Member
Joined
Oct 1, 2014
Messages
8
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
 

Some videos you may like

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
Joined
Apr 8, 2009
Messages
17,649
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

Well-known Member
Joined
Jul 10, 2008
Messages
571
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.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
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
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.

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
Joined
Oct 1, 2014
Messages
8
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:)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,355
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top