VBA for Highlighting Differences from 2 Sheets using Key Identifier

[Ashutosh26]

I have 2 sheets (Sheet1 - New ; Sheet2 - Old) both sheets have a key identifier lets say in column B.

I want to highlight the differences across each key identifier in sheet1 as compared to sheet2.
If key identifier is new to Sheet1 - entire row should be highlighted Red. If there is any change in cell - that particular cell to be marked green.

Also, will it be possible to add a new sheet for summary by what has been changed for each key identifier.

Ex. Sheet 1
Mark | 12 | married
David | 23 | Married
Rob | 34 | Singe

Sheet 2
Mark | 12 | married
David | 23 | Single
Tony | 34 | Singe

Output
Since Rob has been added - entire row to be highlighted in Red
David is married now so Married in David's row gets Green.

Will really appreciate your support.

Logic can be:
Index Key Identifiers in Sheet1 from Sheet2 and compare cell value by looping though the entire row.

 

[mumps]

It would be easier to help if you could post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[JLGWhiz]

Try this

Sub t()
Dim c As Range, i As Long, sh1 As Worksheet, sh2 As Worksheet, fn As Range
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh1.Range("B2", sh1.Cells(Rows.Count, 2).End(xlUp))
        Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                For i = 3 To sh2.Cells(fn.Row, Columns.Count).End(xlToLeft).Column
                    If sh2.Cells(fn.Row, i).Value <> sh1.Cells(c.Row, i).Value Then
                        sh1.Cells(c.Row, i).Interior.Color = vbGreen
                    End If
                Next
            Else
                c.EntireRow.Interior.Color = vbRed
            End If
    Next
End Sub

 

[Ashutosh26]

Thanks JLG
This is working perfectly fine but if Key Identifier row is blank in Sheet2 but having some fields in Sheet1 - it's not highlighted. May be because we have used column.count and in this case its only 1 column.
What is the reason of using i=3 ?

Also, Can we add a new sheet let say sheet3 for actual changes like
In sheet3 :
David | Married <> Single | as in Example.

 

[JLGWhiz]

This fixes the blank cell issue. I did not add the third sheet because it appears to be redundant to the items highlighted green. Am I wrong?

Sub t()
Dim c As Range, i As Long, sh1 As Worksheet, sh2 As Worksheet, fn As Range, cnt As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
    For Each c In sh1.Range("B2", sh1.Cells(Rows.Count, 2).End(xlUp))
        Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
            cnt = Application.Max(sh1.Cells(c.Row, Columns.Count).End(xlToLeft).Column, sh2.Cells(fn.Row, Columns.Count).End(xlToLeft).Column)
                For i = 3 To cnt
                    If sh1.Cells(c.Row, i).Value <> sh2.Cells(fn.Row, i).Value Then
                        sh1.Cells(c.Row, i).Interior.Color = vbGreen
                    End If
                Next
            Else
                c.EntireRow.Interior.Color = vbRed
            End If
    Next
End Sub

 

[Ashutosh26]

You are Brilliant !
It works perfect
In regards to adding a new sheet - Green Highlight is just indicating that this field has been changed.
In new sheet (Lets say Sheet3) - I want excel to display the change itself along with the key identifier.
Example:
David | Single to Married |

 

[JLGWhiz]

I still think it is redundancy, but here it is with sheet 3.

Sub t()
Dim c As Range, i As Long, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, fn As Range, cnt As Long
Set sh1 = Sheets(1) 'Edit sheet name
Set sh2 = Sheets(2) 'Edit sheet name
Set sh3 = Sheets(3) 'Edit sheet name
    For Each c In sh1.Range("B2", sh1.Cells(Rows.Count, 2).End(xlUp))
        Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
            cnt = Application.Max(sh1.Cells(c.Row, Columns.Count).End(xlToLeft).Column, sh2.Cells(fn.Row, Columns.Count).End(xlToLeft).Column)
                For i = 3 To cnt
                    If sh1.Cells(c.Row, i).Value <> sh2.Cells(fn.Row, i).Value Then
                        sh1.Cells(c.Row, i).Interior.Color = vbGreen
                        sh3.Cells(Rows.Count, 1).End(xlUp)(2) = c.Value
                        sh3.Cells(Rows.Count, 1).End(xlUp).Offset(, 1) = "Was: " & sh2.Cells(fn.Row, i).Value
                        sh3.Cells(Rows.Count, 1).End(xlUp).Offset(, 2) = "Is: " & sh2.Cells(c.Row, i).Value
                    End If
                Next
            Else
                c.EntireRow.Interior.Color = vbRed
            End If
    Next
End Sub