comparison macro

**beatrice25** · 10-24-2008, 08:24 PM

Hi All,
Can anyone help me here please....
I need to compare the values of data on one sheet (sheet1) with that of another (sheet2) to see if there has been any changes between the sheets over the previous week. If a macro could go through my data on sheet1, compare cell-by-cell the data that's in sheet 2 and highlight in red font any cells that have different values that would be awesome.
The macro would need to leave the "fill color" of the cell as it was.

Any help much appreciated

Thanks

Beatrice

**mdbct** · 10-24-2008, 09:46 PM

I'm assuming by "cell-by-cell comparison" you mean Sheet1 A1 vs Sheet2 A1.

The following will compare the first two worksheet in the workbook and highlight any cell on the second sheet whose value do not match the corresponding on the first sheet.

Change the number in the worksheets(1) to either the sheet number or the sheet name such as worksheets("sheet1")

Please Login or Register  to view this content.

**mikerickson** · 10-25-2008, 01:20 AM

Perhaps this will do what you want. It sets CF on the cells, rather than looping and changing the cell color.

Please Login or Register  to view this content.

**beatrice25** · 10-27-2008, 05:11 PM

Hi,
these macros seem to work very well so thank you so much. One problem I now have found is that some cells have conditional formatting on them which over-rides the font color change. Would it be possible to include in the macros for the border colors to be changed to red too for the cells with different values as well as the font (which it already changes). Note that the cell fill color has to be left as it is.

Thanks heaps

B.

**mdbct** · 10-27-2008, 09:28 PM

Using the non-conditional formatting method:

Please Login or Register  to view this content.

**beatrice25** · 10-28-2008, 11:35 AM

Hi mdbct,
thanks for the code,
I was playing around with the code yesterday and tried just doing this (copying colorindex line) and it seems to work! What would the downside of this be?Also how do I change the sheet name on this code? to eg shet 1 = max, sheet 2 = mine?

Thanks

B

Please Login or Register  to view this content.

**mdbct** · 10-28-2008, 11:45 AM

From my first post

Change the number in the worksheets(1) to either the sheet number or the sheet name such as worksheets("max")

(altered slightly to use the info you've provided).

I'm not sure I know what you mean

tried just doing this (copying colorindex line) and it seems to work! What would the downside of this be?

Your method of using the Border.ColorIndex is much more compact (I used the macro recorder and got the bloated method. I should have thought about it more instead of taking the "easy way")

**beatrice25** · 10-28-2008, 01:34 PM

Thanks, that was a bit of a numb skull question which I shot off before thinking about.

Thanks for all your help, the macro works very well and now gets rid of discrepancies that have been causing problems in the past.

Is it possible to add this macro to work at the end of a previous macro action
ie. I now have a macro that I recorded to copy and past a sheet then take out some columns before I run the macro that you wrote, can these two be easily combined? The only way i can do it currently is to add some recorded code that selects your macro and runs it, e.g.
Application.Run "'Milestone281008v2.xls'!Highlight"
but this stops working as soon as I change the workbook (Milestone281008v2.xls) name.

Here is what I run before your one:

Please Login or Register  to view this content.

Thanks again mdbct!

B.

**mdbct** · 10-28-2008, 01:57 PM

Please wrap the code in your last two posts.

You can shorten the column deletion code. There is no need to select the columns. Here is a quick example to delete colUmns A, B, F, G, H, P, and q:

Please Login or Register  to view this content.

You should be able to paste my macro to the end of the one you posted. and have it run that way. Remove the End Sub at the end of your macro and the Sub Name() from the beginning of mine.

**beatrice25** · 10-28-2008, 06:29 PM

It's only when one runs one's macro does one see the flaws in it. I found that In need to not only highlight if the values are different between sheet 1 and sheet 2 but also if the interior color is different.
I tried adding the line

Please Login or Register  to view this content.

and added an additional end if, the macro still runs, takes longer to run but doesn't actually highlight a cell where the value on (e.g.) sheet1 cell a1 = the value of sheet2 cell a1 but where the fill color of the cell differs. I would have thought my addition would have done it ...but it doesn't seem to!

**mdbct** · 10-28-2008, 09:08 PM

You were close. You told the macro to check the interior color against the value of the cell. You can use an or statement since either the interior color or the value being different cause a "true"for your if statement.

Please Login or Register  to view this content.

**beatrice25** · 10-29-2008, 11:27 AM

Hi Mdbct,
Thanks for your code, I added it to the existing code and commented out the previous IF statement, however the new code instantly went red and came up with compiling error, do you know what may be causing this?

Please Login or Register  to view this content.

**mdbct** · 10-29-2008, 11:36 AM

It had to do with the wrapping for the "If Or" statement we added.

When wrapping lines in VBA the method is a space, an underscore and then enter. or

Try this version

Please Login or Register  to view this content.

**beatrice25** · 10-29-2008, 03:04 PM

thank you sooooo much, about 10 other people in our engineering group will use this macro and it will totally change our tracking, we will no longer have version management problems as his macro will capture any changes that people make.

B