Comparing Hierarchies

Intrepid121

New Member
Joined
Jul 18, 2016
Messages
2
Hi,

I'm pretty new to Excel, and I'm still not 100% around the functions, so any help, that someone could give me on my question would be much appreciated!

I have 2 sheets with similar hierarchies of 5 levels in the following format:

Sheet1

Row L1 L2 L3 L4 L5
1 a b c d e
2 a b c
3 e d f g h
....

Sheet2 has the same format, but some of the levels have changed values for the level (L1 etc.)

I want to compare the 2 hierachies row by row over the 2 sheets and highlight those rows that have different valuesbetween the hierarchies.

What would be the easiest way to do this?

Thanks in advance
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

bhos123

Well-known Member
Joined
May 2, 2016
Messages
879
Sub Test()
Dim LastCol As Integer
Dim lastrow As Integer
Application.ScreenUpdating = False
Sheets("Sheet1").Select
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
LastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
For i = 2 To lastrow
For j = 1 To LastCol
If Cells(i, j).Value <> Sheets("Sheet2").Cells(i, j).Value Then
Rows(i).Select
Selection.Interior.Color = vbRed
Exit For
End If

Next
Next
Application.ScreenUpdating = True
End Sub
 

Intrepid121

New Member
Joined
Jul 18, 2016
Messages
2
Sub Test()
Dim LastCol As Integer
Dim lastrow As Integer
Application.ScreenUpdating = False
Sheets("Sheet1").Select
lastrow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
LastCol = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
For i = 2 To lastrow
For j = 1 To LastCol
If Cells(i, j).Value <> Sheets("Sheet2").Cells(i, j).Value Then
Rows(i).Select
Selection.Interior.Color = vbRed
Exit For
End If

Next
Next
Application.ScreenUpdating = True
End Sub

Thanks, that works a treat!
 

Forum statistics

Threads
1,144,629
Messages
5,725,384
Members
422,622
Latest member
Paranthem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top