Comparing two sheets. All columns?

[Sladhued]

The code below are for comparing two diffrent sheets in one workbook. it compares rows in column A, and it works excelent. Do anyone know how to make it compare rows through all columns (A-M), and not just A? appriciate any help.

Sub Compare()
Dim LR As Long
Dim x As Range
Dim xr As Range

Application.ScreenUpdating = False

LR = Cells(Rows.Count, 1).End(3).Row

Sheets("Ny").Columns("N:N").Insert Shift:=xlToRight

Set xr = Sheets("Ny").Range("N2:N" & LR)

With xr

.Formula = "=IF(ISNA(VLOOKUP(A2,Aktuell!$A$2:$M$2000,1,false)),""Bold"","""")"
.Value = .Value

End With

For Each x In xr

If x.Value = "Bold" Then x.EntireRow.Font.Bold = True

Next

Sheets("Ny").Columns("N:N").Delete Shift:=xlToLeft



Application.ScreenUpdating = True

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
What exactly are you trying to do?

 

[Sladhued]

Thank you!

Im trying to compare two sheets as you can see in the picture. the changed rows are to be marked bold in "Ny". Also notice that some rows are deleted, and that it should ignore that.

 

[Fluff]

Do you want to make the row bold is any value on that row is different to the Aktuell tab? If not what exactly are the criteria?

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Comparing two sheets. All columns?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Sladhued]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Comparing two sheets. All columns?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Thank you, I understand. wont happen again!

 

[Sladhued]

Do you want to make the row bold is any value on that row is different to the Aktuell tab? If not what exactly are the criteria?

Yes, if any value in any given row is changed, i want it to become bold.

 

[Sladhued]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Comparing two sheets. All columns?
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Cross posted at Comparing two sheets. All columns? aswell. sorry for the inconvenience

 

[Fluff]

Ok, how about

Sub Sladhued()
   Dim Aary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long
   Dim Flg As Boolean
   
   With Sheets("Aktuell")
      Aary = .Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   With Sheets("Ny")
      Nary = .Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   
      For r = 2 To UBound(Nary)
         For rr = 2 To UBound(Aary)
            If Nary(r, 1) = Aary(rr, 1) Then
               Flg = True
               For c = 2 To UBound(Nary, 2)
                  If Nary(r, c) <> Aary(rr, c) Then
                     .Rows(r).Font.Bold = True
                     Exit For
                  End If
               Next c
            End If
            If Flg Then Exit For
         Next rr
         If Flg Then
            Flg = False
         Else
            .Rows(r).Font.Bold = True
         End If
      Next r
   End With
End Sub

 

[Sladhued]

Ok, how about

Sub Sladhued()
   Dim Aary As Variant, Nary As Variant
   Dim r As Long, c As Long, rr As Long
   Dim Flg As Boolean
  
   With Sheets("Aktuell")
      Aary = .Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
   End With
   With Sheets("Ny")
      Nary = .Range("A1:M" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
  
      For r = 2 To UBound(Nary)
         For rr = 2 To UBound(Aary)
            If Nary(r, 1) = Aary(rr, 1) Then
               Flg = True
               For c = 2 To UBound(Nary, 2)
                  If Nary(r, c) <> Aary(rr, c) Then
                     .Rows(r).Font.Bold = True
                     Exit For
                  End If
               Next c
            End If
            If Flg Then Exit For
         Next rr
         If Flg Then
            Flg = False
         Else
            .Rows(r).Font.Bold = True
         End If
      Next r
   End With
End Sub

This is maybe something in the right direction, but it falsely accuses some rows to be changed or different. look at the picture i attach now. In this there should be zero rows that are bold.

Thank you so much for trying to help me