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]

You have multiple rows with the same values in col A, how is a macro meant to know which row it should look at?

 

[Sladhued]

You have multiple rows with the same values in col A, how is a macro meant to know which row it should look at?

I had the same thought as you. I actually didnt think it was possible until I found an Excel plug-in that could do exactly that, its called synkronizer.

I actually made it work now though, with this code:

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

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

and then i add a section of:

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

With xr
       
        .Formula = "=IF(ISNA(VLOOKUP(A2,Aktuell!$A$2:$M$2000,1,false)),""Bold"","""")" 'change the A:s to whatever column you want to compare
        .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

for every column I want to compare. So it looks something like this:

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

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
Set xr = Sheets("Ny").Range("N2:N" & LR)

With xr
       
        .Formula = "=IF(ISNA(VLOOKUP(B2,Aktuell!$B$2:$M$2000,1,false)),""Bold"","""")" 'change the A:s to whatever column you want to compare
        .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

Not entirely sure how its working, but it does.

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[Sladhued]

Okay I have to open this topic again.

the code i have is unreliable, it sometimes misses some rows. Don't know why.

Do anyone have any ideá?

This SHOULD be possible, the app "Synkronizer" can do this without problem, like this: