Need help in comparing two worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

casey - 30 Sep 2007 17:30 GMT

worksheetA = contains some manual entry plus data from monthly file
(worksheetB)
worksheetB = monthly file that gets updated from another system

worksheetC = contains parameters of the search
criteria1 = column C, D, E (primary region secondary region, tertiary
region)
criteria2 = column H, I, J (status, category, type)

worksheetD = contains count from worksheetA
column A = countA (region)
column B = countB (rest)

I need to do the following:
1) compare worksheetA and worksheetB using id_number.
if id_number on B does not exist on A, then add it on the last empty cell on
A
if id_number on B exists on A, compare if column M and N. If there is a
change, highlight it in red.

2) after the above compare, I need to revise the count on worksheetD (column
A and B) based on criteria on worksheetC

I just need a sample code to get me started. Many thanks for everybody's
time.
-Bob

Reply to this Message

Joel - 30 Sep 2007 18:13 GMT

This code should get you started

Sub comparesheet()

With Sheets("Sheet1")
Sh1Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Sh1IDRange = .Columns("A:A")
End With

With Sheets("Sheet2")
Sh2LastRow = _
.Cells(Rows.Count, "A").End(xlUp).Row
Set Sh2IDRange = .Range("A1:A" & Sh2LastRow)
For Each cell In Sh2IDRange
Set c = Sh1IDRange.Find( _
what:=cell, LookIn:=xlValues)
If c Is Nothing Then
Sh1Lastrow = Sh1Lastrow + 1
cell.EntireRow.Copy Destination:= _
Sheets("Sheet1"). _
Rows(Sh1Lastrow)
Else
Criteria_1 = .Cells(cell.Row, "M")
Criteria_2 = .Cells(cell.Row, "N")
With Sheets("Sheet1")

If (.Cells(c.Row, "M") <> Criteria_1) Or _
(.Cells(c.Row, "N") <> Criteria_2) Then

.Cells(c.Row, "M").Interior.ColorIndex = 3
.Cells(c.Row, "N").Interior.ColorIndex = 3
End If
End With
End If
Next cell
End With
End Sub

> worksheetA = contains some manual entry plus data from monthly file
> (worksheetB)
[quoted text clipped - 22 lines]
> time.
> -Bob

Reply to this Message