Compare Two Sheets and Highlight Differences (Sheet attached)

P

PaulNelson

New Member
Joined
Jul 14, 2010
Messages
12
Hi all,

I've been searching for an answer to this question on the internet and there are lots of similar things but nothing does exactly what I want it to do and I'm a bit of a VBA noobie.

I am trying to compare two sheets within the same workbook (Sheet1 = Before and Sheet2 = After) row by row.

I don't want to change anything in the Before sheet, but I want to compare the two and if there are any updates, deletions/modifications between the two sheets, I want to highlight them in yellow on the After sheet. I want the range to be dynamic so that different sets of data can be used with this.

Also, if there could be a pop up that says something along the following lines, it would be awesome! "There were 55 differences detected in the before and after worksheets!"

I hope that's not too much to ask, I know the excel guru's here shouldn't have any problem and the help is greatly appreciated! :)

Here is the link to my sheet as an example of what I want to accomplish. I can provide a bigger data set if required, just let me know!

Example spreadsheet comparison
 
Sort by date Sort by votes
I have modified the routine that calls the compare sheets to handle a scenario whether you want to check what was deleted from the original, what was added, hope it helps someone.
Code: 
Sub RunCompare()
Dim xusedrangeColumns As Integer
Dim yusedrangeColumns As Integer
Dim xusedrangeRows As Integer
Dim yusedrangeRows As Integer
Dim xRow As Range
Dim xColumn As Range
Dim xtext As String
Dim i As Integer
Dim h As Integer

xusedrangeColumns = Sheets("Sheet1").UsedRange.Columns.Count
yusedrangeColumns = Sheets("Sheet2").UsedRange.Columns.Count
xusedrangeRows = Sheets("Sheet1").UsedRange.Rows.Count
yusedrangeRows = Sheets("Sheet2").UsedRange.Rows.Count
Sheets("Sheet1").Cells.Interior.ColorIndex = xlNone
Sheets("Sheet2").Cells.Interior.ColorIndex = xlNone
If xusedrangeColumns = yusedrangeColumns And xusedrangeRows = yusedrangeRows Then
Call compareSheets("Sheet1", "Sheet2")
ElseIf xusedrangeRows <> yusedrangeRows Then
For Each xRow In Sheets("Sheet1").Rows
With Sheets("Sheet1").UsedRange.Rows
  FirstRow = .Row + i
  FirstCol = .Column
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + i
   SecondCol = .Column
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
End With
If Firstvalue = "" And SecondValue = "" Then
Do Until Firstvalue <> ""
With Sheets("Sheet1").UsedRange.Rows
  FirstRow = .Row + i
  FirstCol = .Column + h
 If FirstRow < xusedrangeRows Then
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
   Else
   If (yusedrangeRows - xusedrangeRows) < 0 Then
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Rows in 'Sheet1'; If nothing is yellowed, it's not clear (by rows) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet2' and try again to show the difference among columns")
   Else
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Rows in 'Sheet2'; If nothing is yellowed, it's not clear (by rows) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet1' and try again to show the difference among columns")
   End If
   Exit Sub
   End If
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + i
   SecondCol = .Column + h
   If FirstRow < xusedrangeRows Then
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
   Else
If (yusedrangeRows - xusedrangeRows) < 0 Then
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Row(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by row(s)) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " one row in 'Sheet2' and try again to show the difference among columns")
   Else
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Row(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by row(s)) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet1' and try again to show the difference among columns")
   End If
   Exit Sub
   End If
End With
h = h + 1
Loop
h = 0
If Firstvalue <> SecondValue Then
xRow.Interior.Color = vbYellow
End If
ElseIf Firstvalue <> SecondValue Then
xRow.Interior.Color = vbYellow
End If
i = i + 1
Next
MsgBox ("There are " & (yusedrangeRows - xusedrangeRows) & " extra Row(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by row(s)) where the difference is. Simulate one row in 'Sheet2' and try again to show the difference among columns")
ElseIf xusedrangeColumns <> yusedrangeColumns Then
For Each xColumn In Sheets("Sheet1").Columns
With Sheets("Sheet1").UsedRange.Columns
  FirstRow = .Row
  FirstCol = .Column + i
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row
   SecondCol = .Column + i
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
End With
If Firstvalue = "" And SecondValue = "" Then
Do Until Firstvalue <> ""
With Sheets("Sheet1").UsedRange.Columns
  FirstRow = .Row + h
  FirstCol = .Column + i
 If FirstRow < xusedrangeRows Then
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
   Else
   If (xusedrangeColumns - yusedrangeColumns) < 0 Then
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by column(s)) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet2' and try again to show the difference among rows")
   Else
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by column(s)) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet1' and try again to show the difference among rows")
   End If
   Exit Sub
   End If
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + h
   SecondCol = .Column + i
   If FirstRow < xusedrangeRows Then
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
   Else
 If (xusedrangeColumns - yusedrangeColumns) < 0 Then
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by Columns) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet2' and try again to show the difference among rows")
   Else
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by Columns) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet1' and try again to show the difference among rows")
   End If
   Exit Sub
   End If
End With
h = h + 1
Loop
h = 0
If Firstvalue <> SecondValue Then
xColumn.Interior.Color = vbYellow
End If
ElseIf Firstvalue <> SecondValue Then
xColumn.Interior.Color = vbYellow
End If
i = i + 1
Next
MsgBox ("There are " & (xusedrangeColumns - yusedrangeColumns) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by rows) where the difference is. Simulate one row in 'Sheet2' and try again to show the difference among rows")
End If
End Sub
 
Upvote 0
I am a complete newbie to VB...I love this comparison, but can someone help me with the code to only compare 2 columns in each spreadsheet? They contain numbers only and I don't want the names, addresses, etc. highlighted, only the columns which contain money amounts. Any and all help will be appreciated!
 
Last edited:
Upvote 0
marquez56 said:
I am a complete newbie to VB...I love this comparison, but can someone help me with the code to only compare 2 columns in each spreadsheet? They contain numbers only and I don't want the names, addresses, etc. highlighted, only the columns which contain money amounts. Any and all help will be appreciated!
Click to expand...
Code: 
Sub compare_columns()
Dim i As Integer
Dim xfirst As Integer
Dim xcell As String
Dim cf As Range
Dim originalcell As Range
Dim ToFindRng As Range
Dim ToLookRng As Range
Dim xToFindColumn As Integer
Dim yToFindColumn As Integer
Dim xyToFindRow As Integer
Dim vlookvalue As Range
On Error GoTo x0
Set ToFindRng = Application.InputBox("Select the data to be looked in", "Obtain data to be looked", Type:=8)
xToFindColumn = ToFindRng.Column
On Error GoTo x0
Set ToLookRng = Application.InputBox("Select where data will be searched", "Obtain where data will be searched", Type:=8)
yToFindColumn = ToLookRng.Column
xyToFindRow = ActiveSheet.UsedRange.Rows.Count
Set ToFindRng = Range(Cells(1, xToFindColumn), Cells(xyToFindRow, xToFindColumn))
Set ToLookRng = Range(Cells(1, yToFindColumn), Cells(xyToFindRow, yToFindColumn))
ToLookRng.Select
For Each vlookvalue In ToFindRng
Set cf = Selection.Find(What:=vlookvalue, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        If cf Is Nothing Then
            With vlookvalue.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
        End If
Next vlookvalue
If 1 = 2 Then
x0:
MsgBox ("Please, select a column")
End If
 
End Sub

In these lines

Code: 
Set ToFindRng = Range(Cells(1, xToFindColumn), Cells(xyToFindRow, xToFindColumn))
Set ToLookRng = Range(Cells(1, yToFindColumn), Cells(xyToFindRow, yToFindColumn))

Write before range the sheets you are working with IE:
Code: 
Set ToFindRng = Sheets("sheet1").Range(Cells(1, xToFindColumn), Cells(xyToFindRow, xToFindColumn))
Set ToLookRng = Sheets("sheet1").Range(Cells(1, yToFindColumn), Cells(xyToFindRow, yToFindColumn))
I'm not quite sure if you'd need to change the sheet so it look in the right one
 
Upvote 0
I am using this code and getting an Error
Compile error:
Sub or Function not defined

Any ideas?

Code: 
Sub RunCompare()
Dim xusedrangeColumns As Integer
Dim yusedrangeColumns As Integer
Dim xusedrangeRows As Integer
Dim yusedrangeRows As Integer
Dim xRow As Range
Dim xColumn As Range
Dim xtext As String
Dim i As Integer
Dim h As Integer

xusedrangeColumns = Sheets("Sheet1").UsedRange.Columns.Count
yusedrangeColumns = Sheets("Sheet2").UsedRange.Columns.Count
xusedrangeRows = Sheets("Sheet1").UsedRange.Rows.Count
yusedrangeRows = Sheets("Sheet2").UsedRange.Rows.Count
Sheets("Sheet1").Cells.Interior.ColorIndex = xlNone
Sheets("Sheet2").Cells.Interior.ColorIndex = xlNone
If xusedrangeColumns = yusedrangeColumns And xusedrangeRows = yusedrangeRows Then
Call compareSheets("Sheet1", "Sheet2")
ElseIf xusedrangeRows <> yusedrangeRows Then
For Each xRow In Sheets("Sheet1").Rows
With Sheets("Sheet1").UsedRange.Rows
  FirstRow = .Row + i
  FirstCol = .Column
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + i
   SecondCol = .Column
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
End With
If Firstvalue = "" And SecondValue = "" Then
Do Until Firstvalue <> ""
With Sheets("Sheet1").UsedRange.Rows
  FirstRow = .Row + i
  FirstCol = .Column + h
 If FirstRow < xusedrangeRows Then
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
   Else
   If (yusedrangeRows - xusedrangeRows) < 0 Then
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Rows in 'Sheet1'; If nothing is yellowed, it's not clear (by rows) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet2' and try again to show the difference among columns")
   Else
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Rows in 'Sheet2'; If nothing is yellowed, it's not clear (by rows) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet1' and try again to show the difference among columns")
   End If
   Exit Sub
   End If
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + i
   SecondCol = .Column + h
   If FirstRow < xusedrangeRows Then
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
   Else
If (yusedrangeRows - xusedrangeRows) < 0 Then
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Row(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by row(s)) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " one row in 'Sheet2' and try again to show the difference among columns")
   Else
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Row(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by row(s)) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet1' and try again to show the difference among columns")
   End If
   Exit Sub
   End If
End With
h = h + 1
Loop
h = 0
If Firstvalue <> SecondValue Then
xRow.Interior.Color = vbYellow
End If
ElseIf Firstvalue <> SecondValue Then
xRow.Interior.Color = vbYellow
End If
i = i + 1
Next
MsgBox ("There are " & (yusedrangeRows - xusedrangeRows) & " extra Row(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by row(s)) where the difference is. Simulate one row in 'Sheet2' and try again to show the difference among columns")
ElseIf xusedrangeColumns <> yusedrangeColumns Then
For Each xColumn In Sheets("Sheet1").Columns
With Sheets("Sheet1").UsedRange.Columns
  FirstRow = .Row
  FirstCol = .Column + i
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row
   SecondCol = .Column + i
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
End With
If Firstvalue = "" And SecondValue = "" Then
Do Until Firstvalue <> ""
With Sheets("Sheet1").UsedRange.Columns
  FirstRow = .Row + h
  FirstCol = .Column + i
 If FirstRow < xusedrangeRows Then
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
   Else
   If (xusedrangeColumns - yusedrangeColumns) < 0 Then
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by column(s)) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet2' and try again to show the difference among rows")
   Else
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by column(s)) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet1' and try again to show the difference among rows")
   End If
   Exit Sub
   End If
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + h
   SecondCol = .Column + i
   If FirstRow < xusedrangeRows Then
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
   Else
 If (xusedrangeColumns - yusedrangeColumns) < 0 Then
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by Columns) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet2' and try again to show the difference among rows")
   Else
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by Columns) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet1' and try again to show the difference among rows")
   End If
   Exit Sub
   End If
End With
h = h + 1
Loop
h = 0
If Firstvalue <> SecondValue Then
xColumn.Interior.Color = vbYellow
End If
ElseIf Firstvalue <> SecondValue Then
xColumn.Interior.Color = vbYellow
End If
i = i + 1
Next
MsgBox ("There are " & (xusedrangeColumns - yusedrangeColumns) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by rows) where the difference is. Simulate one row in 'Sheet2' and try again to show the difference among rows")
End If
End Sub
 
Last edited by a moderator:
Upvote 0
rubicon789 said:
I am using this code and getting an Error
Compile error:
Sub or Function not defined

Any ideas?
Click to expand...

Did you copy the original as well?

Code: 
Sub compareSheets(shtBefore As String, shtAfter As String)

Dim mycell As Range
Dim mydiffs As Integer
    
For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then
        
        mycell.Interior.Color = vbYellow
        mydiffs = mydiffs + 1
        
    End If
Next

MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtAfter).Select

End Sub
 
Last edited by a moderator:
Upvote 0
Sgdva,

I am not sure I follow what you are asking? I am totally new to VBA so don't assume anything.

What I copied and pasted is all that was in my code. Do I need to add your comment as a prefix to my code?
 
Upvote 0
Also in same situation as rubicon789.

Trying to use

Code: 
[COLOR=#333333]Sub RunCompare()[/COLOR]Dim xusedrangeColumns As Integer
Dim yusedrangeColumns As Integer
Dim xusedrangeRows As Integer
Dim yusedrangeRows As Integer
Dim xRow As Range
Dim xColumn As Range
Dim xtext As String
Dim i As Integer
Dim h As Integer

xusedrangeColumns = Sheets("Sheet1").UsedRange.Columns.Count
yusedrangeColumns = Sheets("Sheet2").UsedRange.Columns.Count
xusedrangeRows = Sheets("Sheet1").UsedRange.Rows.Count
yusedrangeRows = Sheets("Sheet2").UsedRange.Rows.Count
Sheets("Sheet1").Cells.Interior.ColorIndex = xlNone
Sheets("Sheet2").Cells.Interior.ColorIndex = xlNone
If xusedrangeColumns = yusedrangeColumns And xusedrangeRows = yusedrangeRows Then
Call compareSheets("Sheet1", "Sheet2")
ElseIf xusedrangeRows <> yusedrangeRows Then
For Each xRow In Sheets("Sheet1").Rows
With Sheets("Sheet1").UsedRange.Rows
  FirstRow = .Row + i
  FirstCol = .Column
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + i
   SecondCol = .Column
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
End With
If Firstvalue = "" And SecondValue = "" Then
Do Until Firstvalue <> ""
With Sheets("Sheet1").UsedRange.Rows
  FirstRow = .Row + i
  FirstCol = .Column + h
 If FirstRow < xusedrangeRows Then
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
   Else
   If (yusedrangeRows - xusedrangeRows) < 0 Then
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Rows in 'Sheet1'; If nothing is yellowed, it's not clear (by rows) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet2' and try again to show the difference among columns")
   Else
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Rows in 'Sheet2'; If nothing is yellowed, it's not clear (by rows) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet1' and try again to show the difference among columns")
   End If
   Exit Sub
   End If
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + i
   SecondCol = .Column + h
   If FirstRow < xusedrangeRows Then
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
   Else
If (yusedrangeRows - xusedrangeRows) < 0 Then
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Row(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by row(s)) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " one row in 'Sheet2' and try again to show the difference among columns")
   Else
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Row(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by row(s)) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet1' and try again to show the difference among columns")
   End If
   Exit Sub
   End If
End With
h = h + 1
Loop
h = 0
If Firstvalue <> SecondValue Then
xRow.Interior.Color = vbYellow
End If
ElseIf Firstvalue <> SecondValue Then
xRow.Interior.Color = vbYellow
End If
i = i + 1
Next
MsgBox ("There are " & (yusedrangeRows - xusedrangeRows) & " extra Row(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by row(s)) where the difference is. Simulate one row in 'Sheet2' and try again to show the difference among columns")
ElseIf xusedrangeColumns <> yusedrangeColumns Then
For Each xColumn In Sheets("Sheet1").Columns
With Sheets("Sheet1").UsedRange.Columns
  FirstRow = .Row
  FirstCol = .Column + i
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row
   SecondCol = .Column + i
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
End With
If Firstvalue = "" And SecondValue = "" Then
Do Until Firstvalue <> ""
With Sheets("Sheet1").UsedRange.Columns
  FirstRow = .Row + h
  FirstCol = .Column + i
 If FirstRow < xusedrangeRows Then
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
   Else
   If (xusedrangeColumns - yusedrangeColumns) < 0 Then
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by column(s)) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet2' and try again to show the difference among rows")
   Else
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by column(s)) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet1' and try again to show the difference among rows")
   End If
   Exit Sub
   End If
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + h
   SecondCol = .Column + i
   If FirstRow < xusedrangeRows Then
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
   Else
 If (xusedrangeColumns - yusedrangeColumns) < 0 Then
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by Columns) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet2' and try again to show the difference among rows")
   Else
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by Columns) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet1' and try again to show the difference among rows")
   End If
   Exit Sub
   End If
End With
h = h + 1
Loop
h = 0
If Firstvalue <> SecondValue Then
xColumn.Interior.Color = vbYellow
End If
ElseIf Firstvalue <> SecondValue Then
xColumn.Interior.Color = vbYellow
End If
i = i + 1
Next
MsgBox ("There are " & (xusedrangeColumns - yusedrangeColumns) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by rows) where the difference is. Simulate one row in 'Sheet2' and try again to show the difference among rows")
End If [COLOR=#333333]End Sub[/COLOR]

but get Compile error:
Sub or Function not defined

and I am not sure what is meant by Sgdva's latest post.
 
Upvote 0
You need the code at the end of sdvga's post as well as what you have.
 
Upvote 0
rubicon789 said:
Sgdva,

I am not sure I follow what you are asking? I am totally new to VBA so don't assume anything.

What I copied and pasted is all that was in my code. Do I need to add your comment as a prefix to my code?
Click to expand...

Grimbo said:
Also in same situation as rubicon789.

Trying to use

...
but get Compile error:
Sub or Function not defined

and I am not sure what is meant by Sgdva's latest post.
Click to expand...

OT: I invite you guys to debug the code by pressing F8 to see what each lines does, so you can start learning how to code :biggrin: I'm learning everyday something new and the best practice is to do step by step -you can't stop learning vba for different situations-

RoryA said:
You need the code at the end of sdvga's post as well as what you have.
Click to expand...

If I may, I'll repost the code on how it should be written on the module -It has some lines fixed related to the last one-. Names of sheets need to be "Sheet1" and "Sheet2".

Code: 
Sub RunCompare()
With Excel.Application
.ScreenUpdating = False
.Cursor = xlWait
.Calculation = xlCalculationManual
.StatusBar = False
.DisplayAlerts = False
End With
Dim xusedrangeColumns As Integer
Dim yusedrangeColumns As Integer
Dim xusedrangeRows As Integer
Dim yusedrangeRows As Integer
Dim xRow As Range
Dim xColumn As Range
Dim xtext As String
Dim i As Integer
Dim h As Integer
Sheets("Sheet1").Cells.Interior.ColorIndex = xlNone
Sheets("Sheet2").Cells.Interior.ColorIndex = xlNone

xusedrangeColumns = Sheets("Sheet1").UsedRange.Columns.Count
yusedrangeColumns = Sheets("Sheet2").UsedRange.Columns.Count
xusedrangeRows = Sheets("Sheet1").UsedRange.Rows.Count
yusedrangeRows = Sheets("Sheet2").UsedRange.Rows.Count
 

If (xusedrangeColumns = yusedrangeColumns) And (xusedrangeRows = yusedrangeRows) Then
Call compareSheets("Sheet1", "Sheet2")
ElseIf xusedrangeRows <> yusedrangeRows Then
For Each xRow In Sheets("Sheet1").Rows
With Sheets("Sheet1").UsedRange.Rows
  FirstRow = .Row + i
  FirstCol = .Column
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + i
   SecondCol = .Column
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
End With
If Firstvalue = "" Or SecondValue = "" Then
Do Until Firstvalue <> "" Or (FirstCol + 1 = xusedrangeColumns) Or SecondValue <> ""
With Sheets("Sheet1").UsedRange.Rows
  FirstRow = .Row + i
  FirstCol = .Column + h
 If FirstRow < xusedrangeRows Then
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
   Else
   If (yusedrangeRows - xusedrangeRows) < 0 Then
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Rows in 'Sheet1'; If nothing is yellowed, it's not clear (by rows) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet2' and try again to show the difference among columns")
   Sheets("Sheet2").Select
   Else
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Rows in 'Sheet2'; If nothing is yellowed, it's not clear (by rows) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet1' and try again to show the difference among columns")
   Sheets("Sheet1").Select
   End If
   With Excel.Application
.ScreenUpdating = True
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
.StatusBar = True
.DisplayAlerts = True
End With
   Exit Sub
   End If
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + i
   SecondCol = .Column + h
   If FirstRow < xusedrangeRows Then
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
   Else
If (yusedrangeRows - xusedrangeRows) < 0 Then
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Row(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by row(s)) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet2' and try again to show the difference among columns")
   Sheets("Sheet2").Select
   Else
   MsgBox ("There is/are " & Abs((yusedrangeRows - xusedrangeRows)) & " extra Row(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by row(s)) where the difference begins. Simulate " & Abs((yusedrangeRows - xusedrangeRows)) & " row(s) in 'Sheet1' and try again to show the difference among columns")
   Sheets("Sheet1").Select
   End If
      With Excel.Application
.ScreenUpdating = True
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
.StatusBar = True
.DisplayAlerts = True
End With
   Exit Sub
   End If
End With
h = h + 1
Loop
h = 0
If (Firstvalue <> SecondValue) Or (Firstvalue = "" And SecondValue = "") Then
xRow.Interior.Color = vbYellow
End If
End If
i = i + 1
Next
MsgBox ("There are " & (yusedrangeRows - xusedrangeRows) & " extra Row(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by row(s)) where the difference is. Simulate one row in 'Sheet2' and try again to show the difference among columns")
Sheets("Sheet2").Select
ElseIf xusedrangeColumns <> yusedrangeColumns Then
For Each xColumn In Sheets("Sheet1").Columns
With Sheets("Sheet1").UsedRange.Columns
  FirstRow = .Row
  FirstCol = .Column + i
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row
   SecondCol = .Column + i
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
End With
If Firstvalue = "" Or SecondValue = "" Then
Do Until Firstvalue <> "" Or SecondValue <> ""
With Sheets("Sheet1").UsedRange.Columns
  FirstRow = .Row + h
  FirstCol = .Column + i
 If FirstRow < xusedrangeRows Then
   Firstvalue = Sheets("Sheet1").Cells(FirstRow, FirstCol).Value
   Else
   If (xusedrangeColumns - yusedrangeColumns) < 0 Then
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by column(s)) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet1' and try again to show the difference among rows")
   Sheets("Sheet1").Select
   Else
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by column(s)) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet2' and try again to show the difference among rows")
   Sheets("Sheet2").Select
   End If
      With Excel.Application
.ScreenUpdating = True
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
.StatusBar = True
.DisplayAlerts = True
End With
   Exit Sub
   End If
End With
With Sheets("Sheet2").UsedRange.Columns
   SecondRow = .Row + h
   SecondCol = .Column + i
   If FirstRow < xusedrangeRows Then
   SecondValue = Sheets("Sheet2").Cells(SecondRow, SecondCol).Value
   Else
 If (xusedrangeColumns - yusedrangeColumns) < 0 Then
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by Columns) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet1' and try again to show the difference among rows")
   Sheets("Sheet1").Select
   Else
   MsgBox ("There is/are " & Abs((xusedrangeColumns - yusedrangeColumns)) & " extra Column(s) in 'Sheet1'; If nothing is yellowed, it's not clear (by Columns) where the difference begins. Simulate " & Abs((xusedrangeColumns - yusedrangeColumns)) & " column(s) in 'Sheet2' and try again to show the difference among rows")
   Sheets("Sheet2").Select
   End If
      With Excel.Application
.ScreenUpdating = True
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
.StatusBar = True
.DisplayAlerts = True
End With
   Exit Sub
   End If
End With
h = h + 1
Loop
h = 0
If Firstvalue <> SecondValue Then
xColumn.Interior.Color = vbYellow
End If
End If
i = i + 1
Next
MsgBox ("There are " & (xusedrangeColumns - yusedrangeColumns) & " extra Column(s) in 'Sheet2'; If nothing is yellowed, it's not clear (by rows) where the difference is. Simulate one row in 'Sheet1' and try again to show the difference among rows")
Sheets("Sheet1").Select
End If
   With Excel.Application
.ScreenUpdating = True
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
.StatusBar = True
.DisplayAlerts = True
End With
End Sub
'This works if both sheets have the same size
Sub compareSheets(shtBefore As String, shtAfter As String)
Dim mycell As Range
Dim mydiffs As Double
    
    
For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange
xtext1 = mycell.Value
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then
        
        mycell.Interior.Color = vbYellow
        mydiffs = mydiffs + 1
        
    End If
Next
MsgBox mydiffs & " differences found", vbInformation
ActiveWorkbook.Sheets(shtAfter).Select
End Sub
 
Upvote 0
My issue is this.

Sheet1 has 10 row of data (Call them 1-10)
Sheet2 has 9 rows of data (1-5 and 7-10) missing #6

I want to see that #6 is in Sheet1 but not Sheet2
Now add data in 20 columns in those rows and you will see the idea.
I am not a coder and do want to learn. The way I think of it is the sheet needs to have some key in each row. That key can be used to identify if the data is present or not. Using your code, if I use my example and delete 6 from Sheet2, the Macro highlights where 6 should be and then 7,8,9 and 10 in Sheet2. I want it to see that 7,8,9,10 are in both, so don't highlight those. But tell me that in Sheet1, 6 is highlight because there is a difference between 1 and 2. Does that make sense?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Detecting when a user has highlighted a row and displaying the contents of that row in another sheet
Replies
0
Views
79
ashamy
A
L
  • Question Question
Find the differences between two columns (text)
Replies
1
Views
444
akmatz
A
S
  • Question Question
Compare several columns on two different sheets and copy the differences to another sheet
Replies
5
Views
493
sofas
S
F
  • Question Question
Looking to Set All pivot table filter fields to same selection using VBA
Replies
1
Views
232
jkpieterse
jkpieterse
S
  • Solved
Compare two excel sheets And copy the differences
2
Replies
11
Views
679
sofas
S

Share this page

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
Back
Top