Comparing Hierarchies

[Intrepid121]

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

 

[bhos123]

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]

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!

 

[bhos123]

you are welcome!