Back to Excel VBA archive index

Back to archive home

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.

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 | ||||||

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

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.

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.