Compare two worksheet with new and missing rows and highlight cells of differences

NotSoTechSavvy

New Member
Joined
Jun 4, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Good Day All,

I need some assistance with code that JLGWhiz wrote to complete my task. The task is to select and highlight cells (highlight rows if they are new rows or deleted rows). The code that I copied from JLGWhiz completes 2/3 of my needs, in which it highlights all cells in which there are discrepancies from sheet1 to sheet2, but also recognizes and highlights new rows that have been added to the sheets. However; the problem arises when a row that was in sheet1 is no longer on sheet2, it does not create the row and highlight the missing data.

Please see attached image. Item #F16 & F16.1 are noted on the first sheet, but when F16 was revised on the second it didn't highlight F16.1 as being a deleted item number. Can someone please assist in revising this code to highlight these changes.

Code:
Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, i As Long, c As Range, fn As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
lr = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
For Each c In sh1.Range("A2:A" & lr)
If c <> "" Then
Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
If Not fn Is Nothing Then
For i = 2 To 12
If sh1.Cells(c.Row, i) <> sh2.Cells(fn.Row, i) Then
sh2.Cells(fn.Row, i).Interior.Color = vbYellow
End If
Next
End If
End If
Next
For Each c In sh2.Range("A2", sh2.Cells(Rows.Count, 1).End(xlUp))
If Application.CountIf(sh1.Range("A:A"), c.Value) = 0 Then
c.EntireRow.Interior.Color = vbYellow
End If
Next
End Sub


Thanks you in advance for your assistance.
 

Attachments

  • 1.gif
    1.gif
    156.8 KB · Views: 30

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top