Comparing two excel worksheets Solved/Closed

Question

please, I need a macro code that can actually compare two worksheets and flag up the differences using same color to show the dissimilar rows.


Best Regards,
Wingie.

vcoolio · Answer

Hello Wingie, The following code may help:- Sub HighLightStuff() Application.ScreenUpdating = False Dim lr As Long Dim nCol As Long Dim I As Integer Dim j As Integer lr = Range("A" & Rows.Count).End(xlUp).Row nCol = Cells(2, Columns.Count).End(xlToLeft).Column + 1 Sheet1.Select For I = 2 To lr For j = 1 To nCol If Sheet2.Cells(i, j).Value <> Sheet1.Cells(i, j).Value Then Sheet1.Range(Cells(i, 1), Cells(i, 6)).Interior.ColorIndex = 8 End If Next Next Sheet2.Select For I = 2 To lr For j = 1 To nCol If Sheet1.Cells(i, j).Value <> Sheet2.Cells(i, j).Value Then Sheet2.Range(Cells(i, 1), Cells(i, 6)).Interior.ColorIndex = 8 End If Next Next Sheet1.Select Application.ScreenUpdating = True End Sub Following is the link to my test work book for you to play with:- https://www.dropbox.com/s/vpk65aqbgfyzalo/Wingie%28compare%20two%20sheet%20cell%20values%20%26%20high%20light%29.xlsm?dl=0 There are actually two codes in the work book which do the same job. Click on whichever button to see the respective code at work. Change any of the cell values in either sheet at any time then click either button again. All differences in either sheet will be high-lighted. You can run the code from either sheet. I hope that this helps. Cheerio, vcoolio.

vcoolio · Answer

Hello Wingie,

Starting at the top of the code above:-

Line 1:This is just the name given to the sub routine. You can name it whatever you like.

Line 3: This turns off screen updating which prevents the screen from flickering as the code executes. As a code executes, Excel calculates and updates very, very quickly which causes the screen to flicker.

Lines 5 - 8: Here, the variables are declared. Excel needs to know the data types it has to deal with.

Lines 10 - 11: Here values are set to the rows and columns: lr finds the last used row, nCol finds the next column based on row 2.

Line 13: Start in sheet1.

Lines 14 & 15: values are set to the integers I & j: I for the rows starting at row 2 and going all the way down to the last row, j for the columns stating at the first column and across to however many columns are in the data set. These two combined set the ranges to search.

Lines 17 & 18 : (Line 17):If the values in the cell ranges (i,j) in sheet 2 do not equal the values in the cell ranges in sheet 1, then the whole row of data associated with the non-equal values is high-lighted (Line 18) in colour index 8 (which is an aqua-marine type of colour). You can set this colour to whatever suits you, e.g. 3 for red, 6 for yellow.......

Line 19: Closes off the "If" statement.

Lines 20 & 21: Moves on to the next I and j (or next range) to check.

The remaining lines are just the reverse of the above starting in sheet 2 as a double check for unequal values. Line 34 turns screen updating back on and Line 36 closes off the sub routine.

Hence, if any cell value in sheet 1 does not equal the corresponding cell value in sheet 2, then the relevant row of data is high-lighted in both sheets.

In the sample work book that I supplied in post#1, you'll see that some cell values in sheet 2 are not equal to the corresponding cell values in sheet 1 so these rows are high-lighted. The same will happen in the other direction.

This code is assigned to the dark-blue button in sheet 1 and the orange button in sheet 2 (should you prefer to work from sheet 2) in the sample work book.

In the sample work book, I have supplied another code which does the same job but is probably a little more slick (red button in sheet 1 and light-blue button in sheet 2). Following is that code (it is just another option for you):-

Sub CompareSheets()

Application.ScreenUpdating = False

Dim cell As Range

Sheet1.Select
For Each cell In Sheet2.UsedRange.Offset(1)
    If Not cell.Value = Sheet1.Cells(cell.Row, cell.Column).Value Then
        
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex = 6
        
    End If
Next

Sheet2.Select
For Each cell In Sheet1.UsedRange.Offset(1)
    If Not cell.Value = Sheet2.Cells(cell.Row, cell.Column).Value Then
        
        Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).Interior.ColorIndex = 6
        
    End If
Next

Sheet1.Select
Application.ScreenUpdating = True

End Sub

I hope that this clarifies it for you.

Here, again, is the link to my test work book:-

https://www.dropbox.com/s/vpk65aqbgfyzalo/Wingie%28compare%20two%20sheet%20cell%20values%20%26%20high%20light%29.xlsm?dl=0

Cheerio,
vcoolio.

vcoolio · Answer

Hello Wingie,

In the code in my post#1, change both lines 18 and 28 to:-

Cells(i, j).Interior.ColorIndex = 8


This way, only the individual corresponding cells in both sheets will be high-lighted if one or the other has a different value. This will work for any differing value in any cell.

The test work book is just that and, as you can see, random cells have been changed just so you can see the code at work.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Wingie,

I'm becoming quite confused as to what you are trying to do. In your post#4, you say that you only want the individual cells high-lighted if they have different values and this I resolved for you in my post#5.

However, in your last post #6, you are again saying that you want to high-light a row. Which way do you actually prefer?

pls vcoolio, in the code line; If Sheet2.Cells(i, j).Value <> Sheet1.Cells(i, j).Value Then  Sheet1.Range(Cells(i, 1), Cells(i, 6)).Interior.ColorIndex = 8 the number ^ 6 ^ sets the column boundary to ^ F ^. if I change it, it goes to the corresponding alphabet, it doesn't auto-end in the last column. As in 12=L or 11=K. help me to resolve it.

If your data set extends out to, say, Column K and you want the whole row (A - K) high-lighted, then change this line:-

Sheet1.Range(Cells(i, 1), Cells(i, 6)).Interior.ColorIndex = 8

to

Sheet1.Range(Cells(i, 1), Cells(i, 11)).Interior.ColorIndex = 8

Remember to change it in both lines 18 and 28 in the original code.
Obviously, you can set it to end in whichever column you like and thus it will auto-end in that column.

If you only want to high-light the row from Column A out to the cell with the different value in it, then change this line:-

Sheet1.Range(Cells(i, 1), Cells(i, 6)).Interior.ColorIndex = 8

to

Sheet1.Range(Cells(i, 1), Cells(i, j)).Interior.ColorIndex = 8

Again, change it in both lines 18 and 28.
So, for example, if the cell with the different value, say in sheet 2, is C6, then only A6, B6 & C6 will be high-lighted in both sheets.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Wingie,

A genius I most certainly am not but I'm glad that I was able to help you out. Good luck!

Cheerio,
vcoolio.

Comparing two excel worksheets

5 responses

Similar discussions

Subscribe To Our Newsletter!