Great video! I'm using this code but need help adjust the coding to (1) loop through rows starting from row 4 to the last row and (2) only perform this code on a few of the sheets, not all of them. Can you help? Thanks, in advance!
fwiw I changed the "A1" range reference for ws1 to "A4" so the macro would loop from there through every cell, but it keeps checking all the cells. I also tried to changing the "cell" references to "row" but that just caused errors.
@@rehanchaudhry951 There are several ways to do that. You can use a For loop instead of For Each. For example: For r = 4 To lastRow For c = 1 To lastCol Set cell = Cells(r, c) ... If you want to keep the For Each loop, you need to specify the range, for example for data in columns A to D starting in row 4 as you said would be: For Each cell In ws1.Range("A4:D" & lastRow) ...
If instead of comparing all cells in both Worksheets, suppose I want to compare only 2 specific cells then what change should I make to the code you told above
If it's only 2 cells you can have the 2 conditions but for more than 2 is better to use a range so I show you the code using a range or 2 cells: C3 and H5. You can add more cells there using the comma separator. Then loop for each cell in that range and check if they have the same value in ws1 and ws2. Dim rng As Range, ws1 As Worksheet, ws2 As Worksheet Set rng = Range("C3,H5") Set ws1 = Sheets(1) 'or Sheets("sheetname") or any other way Set ws2 = Sheets(2) For Each cell In rng If ws1.Range(cell.Address) = ws2.Range(cell.Address) Then ws1.Range(cell.Address).Interior.Color = vbYellow End If Next cell
I reply to your similar question in the video about comparing worksheets. For the code in the post - link in description, that would be something like this: Cells(1, cell.Column).Interior.Color = vbYellow
I have 2 sheets in a single work book named March and April. Here 4 columns are there in both the sheets having same names. So the requirement is I have to compare A column of sheet1 with A column in Sheet2 , B column of sheet1 with B column in Sheet2,C column of sheet1 with C column in Sheet2 and D column of sheet1 with D column in Sheet2 and highlight the duplicate cells using Macro VBA. If you can share the code it would be great.
All you need is in these other two videos 1. Highlight duplicates: th-cam.com/video/VohG3ae_A98/w-d-xo.html 2. Compare two worksheets: th-cam.com/video/hZIFnKetyI0/w-d-xo.html
You can use Application.GetOpenFileName or Application.FileDialog. Both are explained towards the mid-end of this video about the Application object: th-cam.com/video/zCMxvxDZ1rw/w-d-xo.html
That's probably because the two workbooks you are comparing are exactly the same, as per the properties that are being compared with this macro (number or sheets, name of sheets, and content cell-to-cell for each sheet with the same name). Otherwise, there might be some error in your code. You can get the code here: excelmacroclass.blogspot.com/2022/02/compare-two-workbooks.html
@@ExcelMacroMania But it seem to be compared with itself. I have tried to read out wb1.Worksheets.Count and wb2.Worksheets.Count. Both are same number of count even their number of sheets are not same. I think the second Workbook(2) is not work. is there any steps that I missed?
Oh, that's why the "second" workbook was actually opened first, so to speak. I wrote Workbooks(2) for simplicity, but is better to specified the exact name of the workbook: Set wb2 = Workbooks("Book2.xlsx"")
Like all your videos...👍
thank you soooo much!
Thank you so much
Great video! I'm using this code but need help adjust the coding to (1) loop through rows starting from row 4 to the last row and (2) only perform this code on a few of the sheets, not all of them. Can you help? Thanks, in advance!
fwiw I changed the "A1" range reference for ws1 to "A4" so the macro would loop from there through every cell, but it keeps checking all the cells. I also tried to changing the "cell" references to "row" but that just caused errors.
@@rehanchaudhry951 There are several ways to do that. You can use a For loop instead of For Each. For example:
For r = 4 To lastRow
For c = 1 To lastCol
Set cell = Cells(r, c)
...
If you want to keep the For Each loop, you need to specify the range, for example for data in columns A to D starting in row 4 as you said would be:
For Each cell In ws1.Range("A4:D" & lastRow)
...
If instead of comparing all cells in both Worksheets, suppose I want to compare only 2 specific cells then what change should I make to the code you told above
If it's only 2 cells you can have the 2 conditions but for more than 2 is better to use a range so I show you the code using a range or 2 cells: C3 and H5. You can add more cells there using the comma separator. Then loop for each cell in that range and check if they have the same value in ws1 and ws2.
Dim rng As Range, ws1 As Worksheet, ws2 As Worksheet
Set rng = Range("C3,H5")
Set ws1 = Sheets(1) 'or Sheets("sheetname") or any other way
Set ws2 = Sheets(2)
For Each cell In rng
If ws1.Range(cell.Address) = ws2.Range(cell.Address) Then
ws1.Range(cell.Address).Interior.Color = vbYellow
End If
Next cell
How to highlight the column header also if any mismatches
I reply to your similar question in the video about comparing worksheets. For the code in the post - link in description, that would be something like this:
Cells(1, cell.Column).Interior.Color = vbYellow
I have 2 sheets in a single work book named March and April.
Here 4 columns are there in both the sheets having same names.
So the requirement is I have to compare A column of sheet1 with A column in Sheet2 , B column of sheet1 with B column in Sheet2,C column of sheet1 with C column in Sheet2 and D column of sheet1 with D column in Sheet2 and highlight the duplicate cells using Macro VBA.
If you can share the code it would be great.
All you need is in these other two videos
1. Highlight duplicates: th-cam.com/video/VohG3ae_A98/w-d-xo.html
2. Compare two worksheets: th-cam.com/video/hZIFnKetyI0/w-d-xo.html
@@ExcelMacroMania I have tried but it is not working correctly. Could you please provide me the updated code that will be really helpful.
Have a look at this new video, I think that's what you are looking for - Compare Two Columns: th-cam.com/video/UvnefIMtNnM/w-d-xo.html
How do i make a dialogue to select the workbboks
You can use Application.GetOpenFileName or Application.FileDialog. Both are explained towards the mid-end of this video about the Application object: th-cam.com/video/zCMxvxDZ1rw/w-d-xo.html
it is not giving any output
nothing is happening when i hit the run
That's probably because the two workbooks you are comparing are exactly the same, as per the properties that are being compared with this macro (number or sheets, name of sheets, and content cell-to-cell for each sheet with the same name). Otherwise, there might be some error in your code. You can get the code here: excelmacroclass.blogspot.com/2022/02/compare-two-workbooks.html
@@ExcelMacroMania But it seem to be compared with itself. I have tried to read out wb1.Worksheets.Count and wb2.Worksheets.Count. Both are same number of count even their number of sheets are not same. I think the second Workbook(2) is not work. is there any steps that I missed?
Oh, that's why the "second" workbook was actually opened first, so to speak. I wrote Workbooks(2) for simplicity, but is better to specified the exact name of the workbook: Set wb2 = Workbooks("Book2.xlsx"")
@@ExcelMacroMania thank you very much for your feedback. it works properly. your video is very helpful ❤