Comparing worksheets with non-identical rows of information

[G. Yamada]

Hi,

I am a newcomer when it comes to functions and programming in Excel which
makes this task all the more difficult. I have a worksheet that is updated
continuously with new/added information and information that is deleted.
This is an inventory worksheet with retail items listed in rows and aspects
of its description located in adjacent cells. Basically, I cannot depend on
an item to be located in the same row each time the worksheet is updated
since things are added and deleted.

What I would like to do is have the original (non-updated) worksheet be
compared to the updated worksheet by:

1. Using the info starting from C3 down (the UPC number for the product) in
the original worksheet and search for this number in the same column (C) in
the updated worksheet.

2. Highlight any differences in columns G, H, I, for the row in question
where the identical UPC numbers are found in both worksheets. (Highlighted in
the original worksheet)

This is a very involved task I would assume, so if an answer cannot be
provided, I would certainly appreciate any suggestions regarding reference
material. Thank you for your time

Glenn Yamada

 

[Joel]

This is a very simple program. Written it plenty of times. Can you let me
know the wroksheets names and the column where the UPC number is located.

I would recommend to do this task the opposite way from the way you
suggested. Go line by line on the updated worksheet and compare to the
original worksheet. This will catch the items that was added to the updated
sheet and not in the original sheet. Because you are requesting to highlight
differences in the updated sheet your way will miss these updated lines
because they don't exist in the original sheet.

 

[G. Yamada]

Hello,

Thank you for your reply. The updated worksheet name is
"updatedDVDdatabase" and the original worksheet name is "DVDdatabase". The
UPCs are located in column C. Thanks again for your help.

Glenn

 

[Joel]

Sub CompareSheets()

With Sheets("updatedDVDdatabase")
RowCount = 3
Do While .Range("C" & RowCount) <> ""
UPC = .Range("C" & RowCount)
With Sheets("DVDdatabase")
Set c = .Columns("C").Find(what:=UPC, _
LookIn:=xlValues, lookat:=xlWhole)
End With
If c Is Nothing Then
.Range("C" & RowCount).Interior.ColorIndex = 3
.Range("G" & RowCount).Interior.ColorIndex = 3
.Range("H" & RowCount).Interior.ColorIndex = 3
.Range("I" & RowCount).Interior.ColorIndex = 3
Else
.Range("C" & RowCount).Interior.ColorIndex = 4
With Sheets("DVDdatabase")
.Range("C" & c.Row).Interior.ColorIndex = 4
End With
For ColCount = Range("G1").Column To _
Range("I1").Column

If .Cells(RowCount, ColCount) = _
Sheets("DVDdatabase").Cells(c.Row, ColCount) Then

.Cells(RowCount, ColCount).Interior.ColorIndex = 4
Sheets("DVDdatabase").Cells(c.Row, ColCount) _
.Interior.ColorIndex = 4
Else
.Cells(RowCount, ColCount).Interior.ColorIndex = 3
Sheets("DVDdatabase").Cells(c.Row, ColCount) _
.Interior.ColorIndex = 3
End If
Next ColCount
End If
RowCount = RowCount + 1
Loop
End With
End Sub

 

[G. Yamada]

I appreciate the help!