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