Compare Sheet1 with Sheet2

[ilyaskazi]

Selected ranges in SHEET1 and SHEET2...

Rows = From 3 onwards upto last found data populated row

Columns = From A to Z

CHECK CELL BY CELL AND FILL COLOR RED FOR THE CELLS IN BOTH SHEETS OF MISMATCH DATA

[shades]

Cross post on Ozgrid. (http://www.ozgrid.com/forum/showthread.php?t=33958)


If you cross post, it is proper netiquette to post the link. But usually you should wait for a reply on another post before cross-posting.

[ilyaskazi]

Well, ok, plz help.

[acw]

Hi



Sub ddd()
sheets("sheet1").select
For Each ce In Range("a1:e7")
If ce.Value <> Sheets("sheet2").Range(ce.Address) Then
ce.Interior.ColorIndex = 3
Sheets("sheet2").Range(ce.Address).Interior.ColorIndex = 3
End If
Next ce
End Sub



This assumes that you know the range to be checked and it has been nominated, and that the other sheet is the same size. You will have to work out how you nominate the range to check - maybe select the range on sheet 1 - and modify the code accordingly.


Tony

[ilyaskazi]

Hi tony,

That was great.
I hv attached one file in which sheets contains names such as?
"NAMES1", "NAMES2", "PHONE1", "PHONE2", "ADDRESS1", "ADDRESS2" and so on.
Here I want to compare "NAMES1" with "NAMES2", "PHONE1" with "PHONE2" and so on.

Workbook may contain any number of sheets by any names.
Macro should detect same name of sheets such as "NAMES", "PHONE" and then compare sheet with sheet1 with sheet2.

[ilyaskazi]

help plzz...

[acw]

HI


Try the attached file. It does assume that there will only be 2 columns of data to check.

Tony

[ilyaskazi]

Hi tony,

your code is great.

but colomns matter me. Your code compares all sheet's rows on basis of the colomn name assigned. I hv now attached my template file exactly as required for my work. Open the attachement for reference..

Now let me tell you in this case, name of the sheet is always constant but may contain additional sheets by different name as well as like... NAMES1, NAMES2, PHONE1, PHONE2, ADDRESS1, ADDRESS2, TEMP, ADDRESS1 (2) and likewise.

NAMES1 with NAMES2, PHONE1 with PHONE2, ADDRESS1 with ADDRESS2 only comparison is required. It means sheet must compare only with its matching names only and must ignore the other different sheets.

Column names are to be assigned for the particular sheets for which it should compare its rows...

NAMES1/NAMES2 = A

PHONE1/PHONE2 = B

ADDRESS1/ADDRESS2 = B and D

On basis of column name A it must compare the rows of NAMES1 with NAMES2, column B for PHONE1 with PHONE2.

But for ADDRESS1 with ADDRESS2 it should compare on basis of column name B and as well as D. Bcoz here sometimes Column B rows may be more than Column D or Column D may be more than column B.

thanking you in anticipation,
ilyas kazi

[acw]

Hi

Have a look at the attached file. I've put in a second macro (bbb). See if that is what you are chasing.

Tony

[ilyaskazi]

tony,

it is not comparing the other columns???

The column names, which i hv given to you is to let your code must know to compare how many rows of

the sheet for ALL COLUMNS.

[ilyaskazi]

continuing post#10...(see previous post)

one more thing, I need to start comparing rows only after row number 3 for all sheets upto its column name assigned.

[ilyaskazi]

urgent help plzzzz...:(

[acw]

Hi


Here we go again.

1) It compares sheets Names, Phone and Address
2) It compares from row 2 to the last row
3) The last row is determined in (a) Names: column A, (b) Phone: Column B, (c) Address: Max row in column B or column D

Sub is bbb.


Tony

[ilyaskazi]

Hi Tony,


The code is getting this work done exactly as I required now but contain one minor error.

Now new code= bbb() compares the rows on basis of first sheet only.

Means, if NAMES1 is having 8 rows and NAMES2 is having less than 8 rows, it gives the output correct.

But if NAMES2 is having more rows than NAMES1, it is not comparing the extra rows of NAMES2 with NAMES1.

Comparison must be based on both sheets and its rows.

In last, display msgbox saying "BAD JOB" if found any single error of any sheet, else "GOOD JOB".

File is attached for your reference.

[acw]

Another go. Still with code bbb

Tony

[ilyaskazi]

Thats Great!!!!

Thankyou, Perfect comparison.

Still msgbox problem is there...

Suppose if your last sheets (ADDRESS1 and ADDRESS2) having no errors, but the other sheets are

having errors, it is displaying msgbox as "GOOD JOB" ???

[ilyaskazi]

msgbox problem...(see previous post)

[acw]

Hi


I don't have that problem. If there is an error in any of the comparisons the bad message appears. I made the names and phones the same, and just had errors in the addresses and the bad job came up. Can you provide details of what was were to get the problem.


Tony

[ilyaskazi]

ok, that problem i hv solved.

now i need to get the cell address to write in txt file.

i hv tried the below code..



Select Case arr(i)
Case "FARES"
Sheets(arr(i) & 1).Select
For Each ce In Range("a3:ab" & WorksheetFunction.Max(Range("a65536").End(xlUp).Row, Sheets(arr(i) & 2).Range("a65536").End(xlUp).Row))
If ce.value <> Sheets(arr(i) & 2).Range(ce.Address) Then
ce.Interior.ColorIndex = 3
ce.Font.ColorIndex = 2
Sheets(arr(i) & 2).Range(ce.Address).Interior.ColorIndex = 3
Sheets(arr(i) & 2).Range(ce.Address).Font.ColorIndex = 2
Open "" + FilePath & "\MYFILE.txt" For Append As #1
j = 1
Do While Not EOF(1) ' Loop until end of file.
Input #1, text
Range("a" & i) = text
j = j + 1
Loop
Print #1, "SHEETNAME= " + ActiveSheet.Name & vbTab & "CELLNAME= " + ActiveCell.Address(False, False)
'Print #1, "" + ce.Address(False, False)
Close #1
bJob = True
Else
gJob = True
End If
Next ce

...tried but not working.

[ilyaskazi]

hey its working...

its my mistake that I was trying in some another way... sorry u all to bother.

and Tony, thanku very much for solving this problem.

[ilyaskazi]

Tony,
One big problem for me is if sheet1 containing cell(s) value is "0" and sheet2 is containing blank cell then output msgbox is "good job".

Here i need to find this as error and show "badjob"

[ilyaskazi]

is this correct??
If ce.value <> Sheets(arr(i) & 2).Range(ce.Address) Or ce.value = "0" And Sheets(arr(i) & 2).Range(ce.Address) = "" Then
i hv made this changes to detect if SHEET1 containing cell value="0" and SHEET2 containing cell value="" (blank cell)
It is working fine..