Comparing two excel worksheets
Solved/Closed
wingie
Posts
1
Registration date
Thursday March 3, 2016
Status
Member
Last seen
March 3, 2016
-
Mar 3, 2016 at 07:59 AM
vcoolio Posts 1364 Registration date Thursday July 24, 2014 Status Moderator Last seen February 10, 2023 - Mar 10, 2016 at 04:05 AM
vcoolio Posts 1364 Registration date Thursday July 24, 2014 Status Moderator Last seen February 10, 2023 - Mar 10, 2016 at 04:05 AM
Related:
- Comparing two excel worksheets
- Compare two worksheets and paste differences to another sheet - excel vba free download ✓ - Excel Forum
- Transfer data from one excel worksheet to another automatically - Guide
- Excel date format dd.mm.yyyy - Guide
- VBA Compare 2 sheets and output difference to 3rd sheet - Excel Forum
- How to compare two Excel sheets with varying data ✓ - Excel Forum
5 replies
vcoolio
Posts
1364
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 10, 2023
252
Mar 4, 2016 at 07:48 AM
Mar 4, 2016 at 07:48 AM
Hello Wingie,
The following code may help:-
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.
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
Posts
1364
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 10, 2023
252
Mar 8, 2016 at 06:31 PM
Mar 8, 2016 at 06:31 PM
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):-
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.
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.
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.
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.
vcoolio
Posts
1364
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 10, 2023
252
Mar 9, 2016 at 06:00 AM
Mar 9, 2016 at 06:00 AM
Hello Wingie,
In the code in my post#1, change both lines 18 and 28 to:-
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.
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
Posts
1364
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 10, 2023
252
Mar 9, 2016 at 07:43 PM
Mar 9, 2016 at 07:43 PM
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?
If your data set extends out to, say, Column K and you want the whole row (A - K) high-lighted, then change this line:-
to
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:-
to
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.
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.
Didn't find the answer you are looking for?
Ask a question
vcoolio
Posts
1364
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 10, 2023
252
Mar 10, 2016 at 04:05 AM
Mar 10, 2016 at 04:05 AM
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.
A genius I most certainly am not but I'm glad that I was able to help you out. Good luck!
Cheerio,
vcoolio.
Mar 8, 2016 at 07:02 AM
kind regards,
Wingie
Mar 9, 2016 at 09:46 AM
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.