VBA to Insert Mismatched Row in separate worksheet

MacOrch

New Member
Joined
Aug 14, 2024
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Long time lurker, first time poster. Usually I can find what I need in previous threads, but this one has beat me.

I have 2 worksheets that I use the below code to compare data in the two.
The code looks for the ID number in Sheet1 ColumnA and matches it to the ID number in Sheet2 ColumnA. Any cells in that row that do not match are highlighted on Sheet2 in yellow. Any missing ID from column A are highlighted red.

My question is how can I copy the entire row of the mismatched cells from Sheet2 and insert it below the corresponding row in Sheet1, and add the missing ID rows the bottom of the data in Sheet1.

Ideally Sheet1 end result would look like this:
2024-08-14_16-21-57.jpg


VBA Code:
Sub Changes()
Range("A1").Select

    Dim ws1 As Worksheet, Ws2 As Worksheet
    Dim ws1Data As Range, f As Range, cell As Range
    Dim icol As Long

    Set ws1Data = Worksheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)

    With Worksheets("Sheet2")
        For Each cell In Intersect(.UsedRange, .Columns(1)).SpecialCells(xlCellTypeConstants)
            Set f = ws1Data.Find(what:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If f Is Nothing Then
                Intersect(cell.EntireRow, .UsedRange).Interior.ColorIndex = 3
            Else
                For icol = 1 To .Range(cell, .Cells(cell.Row, .Columns.Count).End(xlToLeft)).Columns.Count - 1
                    If f.Offset(, icol) <> cell.Offset(, icol) Then
                        cell.Offset(, icol).Interior.ColorIndex = 6
                        
                    End If
                Next icol
            End If
        Next cell
End With
Range("A1").Select

End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,223,703
Messages
6,173,973
Members
452,540
Latest member
haasro02

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