Comparing the same cells in two worksheets.

[hunsnowboarder]

Hi there Everyone!

Firts, thank your for your help in my previous postings! I would like to make a macro which compares the content of the same cells in two worksheets which are in the same workbook.
More clearer I have a workbook with two sheets (Sheet1 and Sheet2). What I would like the macro to do is to check cell A1 in Sheet1 and compare it to cell A1 in Sheet2 and so on till the last cell (IV65536). If there is a differnce between the two cells, then it should highlight the background of the cell in Sheet1.

I know that there has been similar requests on this forum (I searched and read them) but thew were different. That is why I am posting this request!

Thank you for your help in advance and have a nice weekend all!

[ravishankar]

Hi
Attached file has a macro which compares cell to cell between sheet1 and 2 and highlights sheet1 yellow if there is no match
Ravi

[hunsnowboarder]

Hi Ravi! Thank you for your help!

The macro seems to be good however I think I need something a little bit more precise:

1. It only highlights cells in column "A" sheet1. In the other columns it does not highlight them. (for the other columns: B, C, D... the macro is not working).

2. If in cell has content in Sheet2 but not in Sheet1 the macro does not highlight the cell in Sheet1. (Example: if Sheet1, cell A2 is blank, and Sheet2, cell A2 contains something then the macro will not highlight the cell A2 in Sheet1)

Here is your code just for reference:

Please Login or Register  to view this content.

[ravishankar]

Hi
Can you post your workbook? x finds the last row and y finds the last column where your data is. probably row1 has only one cell filled and y becomes 1 and only col A will be compared.
Ravi

[hunsnowboarder]

I do not understand why I have to post it. You made the file..) You can try it as well...

Anyhow I post it for you! Thank you a lot for your help!!!

[ravishankar]

Hi
The reason why I asked you to post your workbook is to see where the problem is with your data. Why BCD are not compared? why A2 if blank is not highlighted. it does not help me if you post a blank workbook with my macro. it works with the data I provide.
Ravi

[hunsnowboarder]

Hi Ravi!
Ok..here is the file. As you can see in cell "A3" and "B3" there is data in both sheets. The data is different in the sheets. If I run your macro, cell "A3" is highlighted (column A). But cell "b3" is not highlighted, however the content of the cells in the two sheets is different!

Thank you a lot for your help!

[hunsnowboarder]

Ohh..sorry..here is the file attached!

[mikerickson]

I think this will do what you want.

Please Login or Register  to view this content.

[hunsnowboarder]

Woohoo! Thanx Mikerickson! This is exactly what I needed!! ) Thanx a lot!

[geertech]

Hi mikerickson,

This is indeed what I am looking for as well, but I've been playing around with your code to have the name of the sheets to be compared taken from a cell on tab 'Import'

I copied and pasted some code from another macro:

Sheets("Import").Select
OldReport = Range("K10").Value
NewReport = Range("K11").Value

Set sheetOne = "OldReport"
Set sheetTwo = ThisWorkbook.Sheets("51")

But (obviously) that doesn't work.

Could you hint me how to solve this?

Also, would it be difficult to get the results on a third sheet, f.i. the value from the first sheet into A1, and the differing value from the second sheet into B1?

Thanks a lot!

Remco

[mikerickson]

Please Login or Register  to view this content.

will take care of sheetOne.

Is the name of sheetTwo 51? Is that what appears on the sheet tab? In that case,

Please Login or Register  to view this content.

Is the tab for sheetTwo the 51st from the left? If so,

Please Login or Register  to view this content.

Is Sheet51 the code name for sheetTwo? From the Project Explorer window?

Please Login or Register  to view this content.

[geertech]

Thanks Mikerickson, that works like a charm!

Is it possible however, to put the results on a separate sheet?
I've tried to change it myself, as I would like to create some sort of chronological change log...


Thanks,

Remco

[mikerickson]

Try
changing this bit

Please Login or Register  to view this content.

[geertech]

Hi mikerickson,

Kind of got this working, at first when changing the code like you suggested, I got a 1004 error, which I've managed to work around changing the code to the following, but it now actually copies 'NewSheet' entirely, and I can't seem to be able to change it to something that only copy/pasts & Highlight just the changes?

Any ideas?

Thanks a lot again!

Please Login or Register  to view this content.

[geertech]

Hi, I've masde a tiny step again myself, in that I have the data copied to the third sheet now, but as mentioned, it copies he complete 'new' sheet, whereas I would like to have just the changed (highlighted) data in the third, preferably with a date stamp.

Thanks!
Remco

This is the section I updated again:

Please Login or Register  to view this content.