Compare two worksheets in the same workbook and highlight difference

[mrcreid]

Hello All,
If anyone could help me with a VBA Macro that will compare two
worksheets in the same workbook and highlight the difference, it would
be greatly appreciated.

Both worksheets have the same number of columns (12) but not the same
number of rows. The first worksheet is called "CURRENT" that has the
current inventory of all our computer assets for the month. The
second worksheet is called "PREVIOUS" that has last month inventory in
it.

Using the serial number column (F2) as the key from current worksheet,
search previous worksheet to see if it exist. If it exist, then
compare the row of data in current worksheet against previous
worksheet where the serial number matches. If they do not match
hightight the entire row in the current worksheet. Also if possible
highlight in read the column cell data that has the mismatch.

The first part i was able to put together, where i uses the serial
number column in current sheet and look to see if it exist in the
previous sheet. If it exist then compare the row, if the rows do not
match, then hightlight the entire row in the current worksheet. What
i am unable to do is the second part, highlight the cell data in the
column where the data in the row does not match.

Listed below is the code thus far.

Sub SearchHighlight()
'--------------------------------------
Sheets("Current").Select
Dim rng As Range
Dim lRow As Long, lRowT As Long
Dim iCol As Integer
Dim bln As Boolean

Set rng = Worksheets("Previous").Range("F2").CurrentRegion
For lRow = 1 To Range("F2").CurrentRegion.Rows.Count
bln = True
For lRowT = 1 To rng.Rows.Count
For iCol = 1 To 12
If Cells(lRow, iCol) <> rng(lRowT, iCol) Then
bln = False
Exit For
End If
Next iCol

If bln = True Then

Exit For

ElseIf lRowT < rng.Rows.Count Then
bln = True
End If
Next lRowT

If bln = False Then
Range(Cells(lRow, 1), Cells(lRow, 12)).Interior.ColorIndex =
6
End If

Next lRow

End Sub

Any and all help with regards to this matter is greatly appreciated.

 

[ryguy7272]

Hello All,
If anyone could help me with a VBA Macro that will compare two
worksheets in the same workbook and highlight the difference, it would
be greatly appreciated.

Both worksheets have the same number of columns (12) but not the same
number of rows.  The first worksheet is called "CURRENT" that has the
current inventory of all our computer assets for the month.  The
second worksheet is called "PREVIOUS" that has last month inventory in
it.

Using the serial number column (F2) as the key from current worksheet,
search previous worksheet to see if it exist.  If it exist, then
compare the row of data in current worksheet against previous
worksheet where the serial number matches.  If they do not match
hightight the entire row in the current worksheet.  Also if possible
highlight in read the column cell data that has the mismatch.

The first part i was able to put together, where i uses the serial
number column in current sheet and look to see if it exist in the
previous sheet.  If it exist then compare the row, if the rows do not
match, then hightlight the entire row in the current worksheet.  What
i am unable to do is the second part, highlight the cell data in the
column where the data in the row does not match.

Listed below is the code thus far.

Sub SearchHighlight()
'--------------------------------------
   Sheets("Current").Select
   Dim rng As Range
   Dim lRow As Long, lRowT As Long
   Dim iCol As Integer
   Dim bln As Boolean

   Set rng = Worksheets("Previous").Range("F2").CurrentRegion
   For lRow = 1 To Range("F2").CurrentRegion.Rows.Count
      bln = True
      For lRowT = 1 To rng.Rows.Count
         For iCol = 1 To 12
            If Cells(lRow, iCol) <> rng(lRowT, iCol) Then
             bln = False
               Exit For
            End If
            Next iCol

         If bln = True Then

            Exit For

         ElseIf lRowT < rng.Rows.Count Then
            bln = True
         End If
      Next lRowT

      If bln = False Then
         Range(Cells(lRow, 1), Cells(lRow, 12)).Interior.ColorIndex =
6
      End If

   Next lRow

End Sub

Any and all help with regards to this matter is greatly appreciated.

I tried 2 ideas below:

Dim itm As Variant
If itm.innterText Like "*SubmitForm_Name*" Then

Dim objCell As Object
If objCell.innerText Like "*SubmitForm_Name*" Then

....sill the same thing.............

 

[ryguy7272]

I tried 2 ideas below:

Dim itm As Variant
If itm.innterText Like "*SubmitForm_Name*" Then

Dim objCell As Object
If objCell.innerText Like "*SubmitForm_Name*" Then

...sill the same thing.............- Hide quoted text -

- Show quoted text -

Whoops, sorry, wrong post!!

 

[ryguy7272]

Whoops, sorry, wrong post!!- Hide quoted text -

- Show quoted text -

Take this for a spin and see how you like it:
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html

Or, perhaps something liek this:
Sub SheetCompare()
' Change the 25 to the last row of your data here
For myRow = 1 To 200
For myCol = 1 To 12
If Sheets("Current").Cells(myRow, myCol).Value <>
Sheets("Previous").Cells(myRow, myCol).Value Then
Sheets("Current").Cells(myRow, myCol).Interior.ColorIndex = 6
Else
Sheets("Current").Cells(myRow, myCol).Interior.ColorIndex = xlNone
End If
Next myCol
Next myRow
End Sub

Of course the # of used rows can be determined dynamically; just a
little sample for you to test...

 

[mrcreid]

Take this for a spin and see how you like it:http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Micros...

Or, perhaps something liek this:
Sub SheetCompare()
' Change the 25 to the last row of your data here
For myRow = 1 To 200
For myCol = 1 To 12
IfSheets("Current").Cells(myRow, myCol).Value <>Sheets("Previous").Cells(myRow, myCol).Value ThenSheets("Current").Cells(myRow, myCol).Interior.ColorIndex = 6
ElseSheets("Current").Cells(myRow, myCol).Interior.ColorIndex = xlNone
End If
Next myCol
Next myRow
End Sub

Of course the # of used rows can be determined dynamically; just a
little sample for you to test...- Hide quoted text -

- Show quoted text -

This appears to work however, not exactly. with the following sample
data

Current
Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Mickey PC-001 A123B456 HP 7900
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Duck Howard PC-005 I678J9012 HP 7900
Doo Scooby PC-006 K345L678 HP 7900
Dog Shaggy PC-007 M901N234 HP 7900

Previous
Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Jerry PC-001 A123B456 HP 7900
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Hawk Junior PC-005 I678J9012 HP 7900

So using the serial number in current sheet, find that serial number
in sheet1. When it finds that serial number, compare the entire row
from Current sheet to Previous sheet. If the entire row matches, do
nothing, and if anything in the rows that do not match, highlight it
in the current sheet...

So in Current sheet the following should be hightlighted

Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Mickey PC-001 A123B456 HP 7900 <- Fname column
<<Mickey>> should be highlighted
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Duck Howard PC-005 I678J9012 HP 7900<<- Lname and
Fname should be hightlighted - name changed
Doo Scooby PC-006 K345L678 HP 7900<- Entire row
Should be hightlighted because not found in Previous sheet
Dog Shaggy PC-007 M901N234 HP 7900<- Entire row
Should be hightlighted because not found in Previous sheet

The code that you provided is very simple, easy, and very short to
what i have. I really like it. the only problem is that in order to
work properly, both sheets (Current and Previous) have to be the same,
if not it will highlight all rows. in the sample above, if i added
another name in the current sheet above Micky Mouse, then all rows
below would be highlighted because it does not match the Previous
sheet. (I hope i explained that right)

Is there a way to modify your code to do this...your assistance is
greatly appreciated.

 

[Jim Cone]

To compare rows, you will have to concatenate the cell values in each
individual row and compare the concatenated values to each other.
That requires a loop thru each row in both worksheets.
--
Jim Cone
Portland, Oregon USA
Compare | match | uniques: http://tinyurl.com/XLCompanion





"mrcreid" <[email protected]>
wrote in message

On Aug 5, 3:20 pm, ryguy7272 <[email protected]> wrote:

This appears to work however, not exactly. with the following sample data

Current
Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Mickey PC-001 A123B456 HP 7900
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Duck Howard PC-005 I678J9012 HP 7900
Doo Scooby PC-006 K345L678 HP 7900
Dog Shaggy PC-007 M901N234 HP 7900

Previous
Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Jerry PC-001 A123B456 HP 7900
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Hawk Junior PC-005 I678J9012 HP 7900

So using the serial number in current sheet, find that serial number
in sheet1. When it finds that serial number, compare the entire row
from Current sheet to Previous sheet. If the entire row matches, do
nothing, and if anything in the rows that do not match, highlight it
in the current sheet...
So in Current sheet the following should be hightlighted

Lname Fname PCName SerialNum Make Model
-----------------------------------------------------------------------------
Mouse Mickey PC-001 A123B456 HP 7900 <- Fname column
<<Mickey>> should be highlighted
Duck Donald PC-002 C789D013 HP 7100
Woman Wonder PC-003 E456F789 HP 7900
Mouse Mighty PC-004 G012H345 HP 7900
Duck Howard PC-005 I678J9012 HP 7900<<- Lname and
Fname should be hightlighted - name changed
Doo Scooby PC-006 K345L678 HP 7900<- Entire row
Should be hightlighted because not found in Previous sheet
Dog Shaggy PC-007 M901N234 HP 7900<- Entire row
Should be hightlighted because not found in Previous sheet

The code that you provided is very simple, easy, and very short to
what i have. I really like it. the only problem is that in order to
work properly, both sheets (Current and Previous) have to be the same,
if not it will highlight all rows. in the sample above, if i added
another name in the current sheet above Micky Mouse, then all rows
below would be highlighted because it does not match the Previous
sheet. (I hope i explained that right)
Is there a way to modify your code to do this...your assistance is
greatly appreciated.

 

[ryguy7272]

To compare rows, you will have to concatenate the cell values in each
individual row and compare the concatenated values to each other.  
That requires a loop thru each row in both worksheets.
--
Jim Cone
Portland, Oregon  USA
Compare | match | uniques:http://tinyurl.com/XLCompanion

"mrcreid" <[email protected]>
wrote in message
This appears to work however, not exactly.  with the following sample data

Current
Lname   Fname  PCName   SerialNum  Make  Model
-----------------------------------------------------------------------------
Mouse   Mickey  PC-001     A123B456  HP      7900
Duck     Donald   PC-002     C789D013  HP      7100
Woman Wonder  PC-003     E456F789  HP      7900
Mouse   Mighty   PC-004     G012H345  HP      7900
Duck     Howard  PC-005     I678J9012   HP      7900
Doo      Scooby   PC-006     K345L678   HP      7900
Dog      Shaggy   PC-007     M901N234  HP      7900

Previous
Lname   Fname  PCName   SerialNum  Make  Model
-----------------------------------------------------------------------------
Mouse   Jerry      PC-001     A123B456  HP      7900
Duck     Donald   PC-002     C789D013  HP      7100
Woman Wonder  PC-003     E456F789  HP      7900
Mouse   Mighty   PC-004     G012H345  HP      7900
Hawk     Junior    PC-005     I678J9012   HP      7900

So using the serial number in current sheet, find that serial number
in sheet1.  When it finds that serial number, compare the entire row
from Current sheet to Previous sheet.  If the entire row matches, do
nothing, and if anything in the rows that do not match, highlight it
in the current sheet...
So in Current sheet the following should be hightlighted

Lname   Fname  PCName   SerialNum  Make  Model
-----------------------------------------------------------------------------
Mouse   Mickey  PC-001     A123B456  HP      7900 <- Fname column
<<Mickey>> should be highlighted
Duck     Donald   PC-002     C789D013  HP      7100
Woman Wonder  PC-003     E456F789  HP      7900
Mouse   Mighty   PC-004     G012H345  HP      7900
Duck     Howard  PC-005     I678J9012   HP      7900<<-Lname and
Fname should be hightlighted - name changed
Doo      Scooby   PC-006     K345L678   HP      7900<- Entire row
Should be hightlighted because not found in Previous sheet
Dog      Shaggy   PC-007     M901N234  HP      7900<-Entire row
Should be hightlighted because not found in Previous sheet

The code that you provided is very simple, easy, and very short to
what i have.  I really like it.  the only problem is that in order to
work properly, both sheets (Current and Previous) have to be the same,
if not it will highlight all rows.  in the sample above, if i added
another name in the current sheet above Micky Mouse, then all rows
below would be highlighted because it does not match the Previous
sheet.  (I hope i explained that right)
Is there a way to modify your code to do this...your assistance is
greatly appreciated.

I think this will do what you want:
Sub Compare()

With Sheets("Current")
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A1:F" & Sh1LastRow)
End With
With Sheets("Previous")
Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = .Range("A1:F" & Sh2LastRow)
End With

'compare sheet 1 with sheet 2
For Each Sh1cell In Sh1Range
Set c = Sh2Range.Find(what:=Sh1cell, LookIn:=xlValues)
If c Is Nothing Then
Sh1cell.Interior.ColorIndex = 3
Sh1cell.EntireRow.Interior.ColorIndex = 3
End If
Next Sh1cell

End Sub

Post back and let everyone know if it meets your business requirement.

 

[mrcreid]

I think this will do what you want:
Sub Compare()

With Sheets("Current")
Sh1LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1Range = .Range("A1:F" & Sh1LastRow)
End With
With Sheets("Previous")
Sh2LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2Range = .Range("A1:F" & Sh2LastRow)
End With

'compare sheet 1 with sheet 2
For Each Sh1cell In Sh1Range
Set c = Sh2Range.Find(what:=Sh1cell, LookIn:=xlValues)
If c Is Nothing Then
Sh1cell.Interior.ColorIndex = 3
Sh1cell.EntireRow.Interior.ColorIndex = 3
End If
Next Sh1cell

End Sub

Post back and let everyone know if it meets your business requirement.

This solution sorta works but not exactly. I need to see if the
Serial Number in sheet2 exist in sheet1, when it finds the serial
number is sheet1, then do the comparison of the row and highlight if
not matching.

My ultimate goal is the following:
-----------------------------------------------
01. Look in Column "D" labeled SerialNum in Sheet2
02. Grab that SerialNum and search in sheet1 to see if it exist
03. Sheet1 may have more rows than sheet2 to or vice versa but the
same number of columns
04. Once it finds the SerialNum in Sheet1, compare Sheet2 row with
Sheet1 row where matching SerialNum, if not matching highlight
mismatch column in that row
05. If SerialNum in sheet2 does not exist in Sheet1, hightlight
SerialNum in sheet2 in different color (Green)
06. Extra -> where mismatching, add a comment tag in sheet2 that
illustrates the mismatching data.

The problem that i am having with the provided code, is that it does
not do a search of the SerialNum and compares that row. I copied and
pasted the provided code and that's what i keep getting.

I will try again and let you know what i get....