Comparing columns and highlighting

[gmoorthy]

I have 2 spreadsheets ( sheet 1 and Sheet 2). I wrote a macro which compares the data between two sheets and highights the difference . The macro is given below which works , but the issue is that the columns in sheet2 is changing for ex:

In Sheet 1

Name Zipcode email



In sheet 2

Name Adress Zipcode


So i want to compare zipcode with zipocde and then highlight the difference in Sheet 2 . The first column Name does not change in Sheet 1 and Sheet 2.

Below is the macro that i used.

Sub RunCompare()

Call compareSheets("Sheet1", "Sheet2")

End Sub


Sub compareSheets(shtSheet1 As String, shtSheet2 As String)

Dim mycell As Range
Dim mydiffs As Integer

'For each cell in sheet2 that is not the same in Sheet1, color it yellow
For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange
If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then

mycell.Interior.Color = vbYellow
mydiffs = mydiffs + 1

End If
Next

'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtSheet2).Select

End Sub

 

[SkipVought]

Please post a short example of the DATA in each sheet and using that example explain EXACTLY what result you expect and what result you currently have from your code.

Please answer each and every point raised above.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

How do i attach a spreadsheet with the sample data

 

[SkipVought]

Just copy & paste here. Sanitize your data if necessary.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

You need to compare two columns only. I would add them to comparesheets as additional parameters: as arguments, as variables, or as constants.
Assuming the second option:

Sub compareSheets(shtSheet1 As String, shtSheet2 As String)
...
dim rngSearchInSheet2 as Range
ZipCodeCol1=2
ZipCodeCol2=3
set rngSearchInSheet2=Intersect(ActiveWorkbook.Worksheets(shtSheet2).UsedRange,ActiveWorkbook.Worksheets(shtSheet2).Columns(ZipCodeCol2))
If Not rngSearchInSheet2 is Nothing Then
For Each mycell In rngSearchInSheet2
If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, ZipCodeCol1).Value Then
....
End If

You may also consider using ThisWorkbook instead of ActiveWorkbook if the code is in the same workbook as data, or explicit referencing if no.




combo

 

[gmoorthy]

Thanks for the macro . I have multiple columns which i would need to match there are approximately 120 columns and the order of the columns in the sheet2 changes.

 

[SkipVought]

Not very clear what you are asking.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[combo]

You need to map headers somehow. Hardcoded in the macro, table in the worksheet or searching headers if the same and building pairs of headers.

combo

 

[gmoorthy]

Here is the data on Sheet1

Name Zipcode email
ABC 1234 x@yahoo.com
XYZ 4567 y@yahoo.com

Here is the data on Sheet2

Name Address Zipcode
ABC TTTTTT 7890
XYZ YYYYYY 2390

Like this i have to match 120 columns . Where on Sheet2 the order of the columns could change from sheet1. So was trying to see if there a way to do this automatically instead of hard coding. If you also see column 2 ( Address ) which is in sheet2 is not there in sheet 1 and "email" which is on sheet1 is not there in sheet2 i am not worried about that.

 

[SkipVought]

You show ONE SET OF COLUMNS on Sheet1 and ONE SET OF COLUMNS on Sheet2.

Where is your example of MULTIPLE SETS (certainly not 120 SETS, be lets say 3 SETS)???

Or am I out-to-lunch?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

I cant post the entire 120 columns here.

 

[SkipVought]

I cant post the entire 120 columns here.

certainly not 120 SETS, be lets say 3 SETS

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

Sheet1

Name Zipcode email Date Amount Credit
ABC 1234 x@yahoo.com 12/31/2002 123 No
XYZ 4567 y@yahoo.com 12/31/2002 456 Yes

Sheet2

Name Address Zipcode email Date Credit Amount
ABC TYU 1234 x@yahoo.com 12/31/2002 Yes 789
XYZ MNO 9000 y@gamil.com 12/31/2002 Yes 456

 

[SkipVought]

So what you are saying is NOT that NAME & ZIPCODE is in multiple columns, but that NAME & ZIPCODE could appear in any one of the 120 columns. YES?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

yes that is correct luckily the NAME in sheet 1 and sheet 2 are in column A so far.

 

[SkipVought]

So could you not simply SEARCH for Zipcode to determine the column and then search that column?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[gmoorthy]

It is not just zipcode , i have to compare every column in sheet1 and if a match for the column header is found in sheet2 compare the data and then highlight the differences in sheet 2

 

[SkipVought]

Are you comparing ALL the data in Row_n on both sheets, so the compare is ROW BY ROW?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}