Report

Comparing two excel worksheets [Solved]

Ask a question wingie 1Posts Thursday March 3, 2016Registration date March 3, 2016 Last seen - Latest answer on Mar 10, 2016 04:05AM
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.
See more 
Helpful
+0
moins plus
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.
wingie- Mar 8, 2016 07:02AM
pls vcoolio, ur code got me confused. can u pls elaborate more. I just need 2 understand the code better. thanks

kind regards,
Wingie
Reply
Wingie- Mar 9, 2016 09:46AM
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.
Reply
Add comment
Helpful
+0
moins plus
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.
wingie- Mar 9, 2016 05:38AM
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.

Pls, in line 17 and 18, how do I edit the code to highlight the particular cells with the differences? I want the macro to highlight cell F5 in sheet 1 and cell F5 in sheet 2 if there is a difference in the cell F5.

thanks 4 your concern, I really appreciate.
Reply
Add comment
Helpful
+0
moins plus
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.
Add comment
Helpful
+0
moins plus
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.
Wingie- Mar 10, 2016 03:13AM
Well done VCoolio, you are a genius. Thanks for the help.

kind regards,
Wingie.
Reply
Add comment
Helpful
+0
moins plus
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.
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!