PDA

View Full Version : Compare 2 different excel sheets



Shreenidhi
01-21-2017, 03:04 AM
Hi everyone,
I need your help to create a VB program in excel. Here goes the description.
Excel Sheet A is a comment sheet which contains ‘n’ number of consolidated comments by the reviewer on many of the previous technical reports.
Excel Sheet B is a prepared technical report.
I need your help to write a code to check whether all the comments in Sheet A are incorporated in Sheet B. If not the mistake should be highlighted.
Request you to help me in this.
Thanks.

Logit
01-21-2017, 08:02 PM
.
Paste this code into a routine module. Activate it with a commandbutton on Sheet 1 :





Public Sub CompareSheets()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cell As Range, rng As Range
Dim Celladdress
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set rng = ws1.Range("A1:Z200")
For Each cell In rng
Celladdress = cell.Address
If cell <> ws2.Range(Celladdress) Then
cell.Interior.Color = vbYellow
ws2.Range(Celladdress).Interior.Color = vbYellow
End If
Next cell

End Sub

Shreenidhi
01-21-2017, 10:51 PM
Dear Logit,

I thank you for the program.

But in my case both sheet 1 and sheet 2 are totally different.

The program below is valid for similar sheets right?





.
Paste this code into a routine module. Activate it with a commandbutton on Sheet 1 :





Public Sub CompareSheets()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim cell As Range, rng As Range
Dim Celladdress
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
Set rng = ws1.Range("A1:Z200")
For Each cell In rng
Celladdress = cell.Address
If cell <> ws2.Range(Celladdress) Then
cell.Interior.Color = vbYellow
ws2.Range(Celladdress).Interior.Color = vbYellow
End If
Next cell

End Sub

Logit
01-22-2017, 07:33 AM
Similar ... yes.

If your two sheets are different it would be best to post a sampling in a workbook of what you are dealing with. Leave out any confidential data. That way someone can assist
you with your exact circumstance.