VBA code to Compare column A of two sheets and insert rows for missing numbers on sheet 2

[Curtisyoung78]

Hello again guys, I need your help with some vba coding That will compare Column A on sheet 1 and 2 which is a Pile Schedule (sheets are revisions), listing pile numbers in cloumn A, if a pile is not included on sheet 2 (Has Been Deleted) then I need it to insert a row where the missing pile number would be and show that pile number in column A, then add "-" in cells B thru K, P & Q and "NOT USED" in column L.

Just need this so that sheet 1 and 2 have the same numbers in column A when i run a macro that compares both sheets to bold the differences in cells. If the piles that have been deleted are not included on sheet 2 then all piles after them would be bolded. I bold the cells that have changed between revisions to have them stand out.

Thanks in advance.

 

[Fluff]

Try this

Sub FindMissing()

    Dim Cnt As Long
    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    
    Set Sht1 = Sheets("Summary")
    Set Sht2 = Sheets("Test1")
    
    For Cnt = 2 To Sht1.Range("A" & Rows.Count).End(xlUp).Row
        If Sht1.Range("A" & Cnt).Value <> Sht2.Range("A" & Cnt) Then
            Sht2.Rows(Cnt).Insert
            Sht2.Range("A" & Cnt).Value = Sht1.Range("A" & Cnt).Value
            Sht2.Range("B" & Cnt).Resize(, 10).Value = "-"
            Sht2.Range("L" & Cnt).Value = "NOT USED"
            Sht2.Range("P" & Cnt).Resize(, 2).Value = "-"
        End If
    Next Cnt

End Sub

 

[Curtisyoung78]

Thanks Fluff, it works but i forgot to mention that it needs to work from row 12 down, Sorry. Can you tweek it to work from row 12 down to the end. I tried a few things but my VBA skills are to limited. Thanks in advance.

 

[Fluff]

Simply change

For Cnt = 2

from 2 to 12

 

[Curtisyoung78]

Works perfectly, thanks Fluff.

 

[Fluff]

Glad to help & thanks for the feedback

 

[torrino1]

Fluff:

I there a way to modify the code to skip items in the master list, if they are not In the list your comparing to?
I use the code but seem to be running into an issue, as I have a master list and if I run the VBA I will insert all rows if the item is on the master instead of skipping if not located

 

[Fluff]

As the code was designed to do that, your question is different, so please start a new thread.