Can you expand on what "nothing happens" means?

But before you do that, look at the other sheet. Maybe you weren't looking at
the right one.

If that doesn't help, go back to the VBE. Put the cursor somewhere in that code
and hit F8 and step through the code. Maybe you'll see what's happening.

Roberto R wrote:

Hi Dave, thanks for the lightning quick answer. I opened up the VB editor
and pasted your lines in. I think it created a macro called "testme". I
ensured the column headers were in row1 on both sheets and modified the
lines you suggested to put exactly the names of the worksheets but nothing
happens when I run the macro itself!

I must be doing soemthing wrong but I can't figure out what! Any
suggestions? I hope to get it working because it sounds like your macro does
exactly what I reuire!!

Thanks again,
Roberto R

"Dave Peterson" wrote in message
...
So the Part numbers are unique and never duplicated (on each sheet),
right?

Here's one I saved that kind of does what you want.

But just to make sure, create a new workbook and put the two sheets into
that
new workbook. Put headers in row 1 of each sheet if you don't have them
there
already.

Modify this pair of lines:
Set MstrWks = ActiveWorkbook.Worksheets("sheet1")
Set NewWks = ActiveWorkbook.Worksheets("sheet2")

to point at the worksheet names you use.

Mstrwks will become the "report" worksheet.

It removes the fill color and marks the differences in red (.colorindex
=3).

It also adds an indicator in a column with the change type. You can
filter on
that later????

And if you added more records in Sheet2, it'll add them to the bottom and
mark
them "added".

And if the key was missing from the other sheet, it puts a message there,
too.

Make sure you try it out against a test copy of your workbook--just in
case (or
close without saving if it breaks things too much!).


Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim MstrWks As Worksheet
Dim NewWks As Worksheet

Dim MstrKeyRange As Range
Dim NewKeyRange As Range
Dim myCell As Range
Dim destCell As Range

Dim LastCol As Long

Dim iCol As Long
Dim res As Variant

Set MstrWks = ActiveWorkbook.Worksheets("sheet1")
Set NewWks = ActiveWorkbook.Worksheets("sheet2")

With MstrWks
Set MstrKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
.Cells.Interior.ColorIndex = xlNone 'remove all fill color!
End With

With NewWks
Set NewKeyRange = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
End With

LastCol = 6 'A to F
MstrWks.Columns(LastCol + 1).Clear
For Each myCell In MstrKeyRange.Cells
With myCell
res = Application.Match(.Value, NewKeyRange, 0)
If IsError(res) Then
.Parent.Cells(myCell.Row, LastCol + 1).Value _
= "Not on other sheet"
Else
For iCol = 1 To LastCol - 1
If .Offset(0, iCol).Value _
= NewKeyRange(res).Offset(0, iCol).Value Then
'do nothing, they match
Else
.Offset(0, iCol).Value _
= NewKeyRange(res).Offset(0, iCol).Value
.Offset(0, iCol).Interior.ColorIndex = 3
.Parent.Cells(myCell.Row, LastCol + 1).Value _
= "Changed"
End If
Next iCol
End If
End With
Next myCell

'check for newly added entries
For Each myCell In NewKeyRange.Cells
With myCell
res = Application.Match(.Value, MstrKeyRange, 0)
If IsError(res) Then
'missing from new workbook!
With MstrWks
Set destCell _
= .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
.Resize(1, LastCol).Copy _
Destination:=destCell
destCell.Parent.Cells(destCell.Row, LastCol + 1).Value _
= "Added"
Else
'already in the master
'don't do anything
End If
End With
Next myCell

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Roberto R wrote:

Hi Guys,
I'm new to this workgroup and fairly new to Excel so please be payient.

I have reports sent to me in excel on a monthly basis which I need to
compare. The reports are basically horizontal data tables of parts that
our
company sells. The worksheets contains approximately 7000 rows of
data.The
column headings are as follows.

Part Number
Description
Annual Sales Volume
Technical features
Price
Customer

What I would like to do is find an automaitc way of comapring say the
"November" data to the previous "October" one. Changes normally occurr on
the existing data or indeed rows are added (for new parts) or deleted
(for
redundent ones).

What (I think) would absolutely ideal would be a function or something
that
compares the same parts on the 2 reports by Part Number and reports the
following:

Changes in any of the other columns corresponding to the part number
compared
New part part number not present on the previous month's report
Part numbers presented on the previous report but deleted on the new one.

I hope this all makes sense to someone and I've been told Excel "can do
anything" so I'm hopefull!!!

I would also really appreciate it if any replies could be copied to my
email
address as I do not have access to this newsgroup at work.

Thanks very much in advance to all who want to help!!

Roberto R

--

Dave Peterson

--

Dave Peterson