Archive of Mr Excel Message Board


Back to Excel VBA archive index
Back to archive home



COMPARE TWO SPREADSHEETS AND HIGH LIGHT THE DIFFERENCES

Posted by YANECKC on September 06, 2001 1:12 PM
I HAVE TWO SPREADSHEETS (NOSHOW1 AND NOSHOW2)
EACH ROW HAS FOUR COLUMNS OF INFORMATION.
I WOULD LIKE TO COMPARE THE NOSHOW1 AGAINST THE NOSHOW2.
IF ANY CELL IS DIFFERENT I WOULD LIKE THE NOSHOW1 TO
HAVE ALL THE DIFFERENCES HIGHLIGHTED.

BELOW IS A COPY OF THE NOSHOW1 AND NOSHOW2

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

BY COMPARING THE NOSHOW1 AGAINST NOSHOW2 AND HIGHLIGHTING THE DIFFERENCES
ON NOSHOW1 I WILL KNOW IF AN ID # CAME OFF THE NOSHOW2 AND IT WILL SHOW
CHANGES MADE BY HIGHLIGHTING THEM.

YANECKC


Check out our Excel VBA Resources

Re: COMPARE TWO SPREADSHEETS AND HIGH LIGHT THE DIFFERENCES

Posted by Ben O. on September 06, 2001 2:18 PM
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.


Re: COMPARE TWO SPREADSHEETS AND HIGH LIGHT THE DIFFERENCES

Posted by yaneckc on September 06, 2001 2:50 PM

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


Re: COMPARE TWO SPREADSHEETS AND HIGH LIGHT THE DIFFERENCES

Posted by Aladin Akyurek on September 06, 2001 3:28 PM
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


Re: COMPARE TWO SPREADSHEETS AND HIGH LIGHT THE DIFFERENCES

Posted by yaneckc on September 07, 2001 6:57 AM

Aladin

Yes that is correct. That is what I want.

yaneckc


Re: COMPARE TWO SPREADSHEETS AND HIGH LIGHT THE DIFFERENCES

Posted by Aladin Akyurek on September 07, 2001 10:23 AM

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.


Re: COMPARE TWO SPREADSHEETS AND HIGH LIGHT THE DIFFERENCES

Posted by YANECKC on September 07, 2001 11:56 AM

ALADIN

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

THANKS YANECKC




This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.