COMPARE TWO SPREADSHEETS AND HIGH LIGHT THE DIFFERENCES

Yaneck,

This macro should do the trick:

Sub SheetCompare()
' Change the 25 to the last row of your data here
For myRow = 1 To 25
For myCol = 1 To 4
If Sheets("NOSHOW1").Cells(myRow, myCol).Value <> Sheets("NOSHOW2").Cells(myRow, myCol).Value Then
Sheets("NOSHOW1").Cells(myRow, myCol).Interior.ColorIndex = 6
Else
Sheets("NOSHOW1").Cells(myRow, myCol).Interior.ColorIndex = xlNone
End If
Next myCol
Next myRow
End Sub

-Ben O.

BEN

IT HIGHLIGHTED ALOT OF DATA THE WAS THE SAME
AND IT SHOULD NOT BE HIGHLIGHTED IF IT FOUND A MATCH.
THE PROBLEM I THINK IS IT HAS TO SEACRH THE ENTIRE COLUMN OF NOSHOW2, ONE ROW AT A TIME
BECAUSE THE INFORMATION IS NOT ALWAYS ON THE SAME ROW.

YANECKC

Yaneck,

Lets say that we have in row N on NOSHOW1

12345N 17B8 C678 MISCELL PARTS

and in row M on NOSHOW2

12345N 17B8 X78 MISCELL PARTS

Obviously, these rows differ regarding Ven#.

It is my understanding that you want the cell of C678 to be highlighted.

Is this right?

Aladin

Aladin

Yes that is correct. That is what I want.

yaneckc

On the NOSHOH2 do the following:

Select all the cells containing ID#s (excluding the column heading/label) and name the selected range IDS via the Name Box on the Formula Bar.
Select all the cells containing data (excluding column headings/labels) and name the selected range DATA.

To illustrate, lets have the followin sample in A1:D7 on NOSHOW1

{"ID#","Product-CODE","VEN#","DESCRIPTION";
"12345N","17B8","C678","MISCELL PARTS";
"23B566","17B8","C698","MISCELL PRT GF";
"456T67","17B8","T789","COMPUTER ASCESS";
"47897J","56YU","G987","TAX PRD MISCELL";
"57I98P","875K","FYT6","FAX PRODUCTS";
"6789OR","234T","7DFG","AMERIC PRODUCTS"}

and the following sample data in A1:D7 NOSHOW2

{"ID#","Product-CODE","VEN#","DESCRIPTION";
"12345N","17B8","x76","MISCELL PARTS";
"23B566","17B8","C698","MISCELL PRT GF";
"456T67","17B8","T789","COMPUTER ASCESS";
"47897J","56YU","Y76","FAX PRODUCTS";
"57I98P","875K","FYT6","FAX PRODUCTS";
"6789OR","234T","7DFG","AMERIC PRODUCTS"}

Note. It's accidental that ranges on both sheets are equal in size and that the addresses look similar.

Note. With respect to the above sample, the name IDS refers to the range A2:A7 and DATA to the range A2:D7.

Note. Naming the indicated ranges is essential to what follows.

On NOSHOW1:

Activate B2.
Activate the option Format|Conditional Formatting.
Choose "Formula is" for Condition 1 in the Conditional Formatting window.
Enter the following formula:

=AND(ISNUMBER(MATCH(A2,IDS,0)),B2<>VLOOKUP(A2,DATA,2,0))

Note. No $-signs wrt A2 and B2 in the above formula and in others below.

Activate Format.
Chose a formatting that you want.
Activate OK.

While in B2, click on the icon for Format Painter, select the rest of the B range, and let it go.

Activate C2 and follow the procedure just described.
The formula to be used is:

=AND(ISNUMBER(MATCH(A2,IDS,0)),C2<>VLOOKUP(A2,DATA,3,0))

Activate D2 and follow the procedure.
The formula to be used is:

=AND(ISNUMBER(MATCH(A2,IDS,0)),D2<>VLOOKUP(A2,DATA,4,0))

Aladin

PS. If interested to have a copy of the workbook containing all of the above, just drop me a line.

ALADIN

CAN YOU EMAIL ME A COPY OF THE WORKSHEET TO YANECKC@AOL.COM.

THANKS YANECKC