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.

On Aug 5, 2:09*pm, mrcreid wrote:
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.............

On Aug 5, 3:20*pm, ryguy7272 wrote:
On Aug 5, 2:09*pm, mrcreid wrote:





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.............- Hide quoted text -

- Show quoted text -

Whoops, sorry, wrong post!!

On Aug 5, 3:20*pm, ryguy7272 wrote:
On Aug 5, 3:20*pm, ryguy7272 wrote:





On Aug 5, 2:09*pm, mrcreid wrote:

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.............- Hide quoted text -

- Show quoted text -

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_t...ce l/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...

On Aug 5, 2:41*pm, ryguy7272 wrote:
On Aug 5, 3:20*pm, ryguy7272 wrote:





On Aug 5, 3:20*pm, ryguy7272 wrote:

On Aug 5, 2:09*pm, mrcreid wrote:

Hello All,
If anyone could help me with a VBA Macro that willcomparetwo
worksheets in the same workbook andhighlightthedifference, 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
comparethe 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
highlightin 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 thencomparethe 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,highlightthe 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.............- Hide quoted text -

- Show quoted text -

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_t..._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.

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"
wrote in message
...
On Aug 5, 2:41 pm, ryguy7272 wrote:
On Aug 5, 3:20 pm, ryguy7272 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.

On Aug 6, 10:50*am, "Jim Cone" wrote:
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"
wrote in ...
On Aug 5, 2:41 pm, ryguy7272 wrote: On Aug 5, 3:20 pm, ryguy7272 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.


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.

On Aug 6, 11:19*pm, ryguy7272 wrote:
On Aug 6, 10:50*am, "Jim Cone" wrote:

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"
wrote in ...
On Aug 5, 2:41 pm, ryguy7272 wrote: On Aug 5, 3:20 pm, ryguy7272 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.

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....