Как сравнить 2 таблицы с ценами, обозначить изменения и внести поправки.

Sub m_1()
Dim LastRowCENA_TOVARA As Long
Dim LastRowPROVERKA As Long
Dim oCell As Excel.Range
Dim oFindCell As Excel.Range
LastRowCENA_TOVARA = Worksheets("CENA_TOVARA").Cells.SpecialCells(xlCellTypeLastCell).Row
LastRowPROVERKA = Worksheets("PROVERKA").Cells.SpecialCells(xlCellTypeLastCell).Row
For Each oCell In Worksheets("CENA_TOVARA").Range("A1:A" & LastRowCENA_TOVARA)
    Set oFindCell = Worksheets("PROVERKA").Range("A1:A" & LastRowPROVERKA). _
        Find(What:=oCell.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not oFindCell Is Nothing Then
            If oFindCell.Offset(0, 5).Value > oCell.Offset(0, 5).Value Then
                oCell.Font.Color = -16776961
                oCell.Offset(0, 5).Interior.Color = -16776961
            ElseIf oFindCell.Offset(0, 5).Value < oCell.Offset(0, 5).Value Then
                oCell.Font.Color = -11489280
                oCell.Offset(0, 5).Interior.Color = -11489280
            End If
        End If
Next oCell
End Sub

Sub Ìàêðîñ3()
Dim LastRowCENA_TOVARA As Long
Dim LastRowPROVERKA As Long
Dim oCell As Excel.Range
Dim oFindCell As Excel.Range
LastRowCENA_TOVARA = Worksheets("CENA_TOVARA").Cells.SpecialCells(xlCellTypeLastCell).Row
LastRowPROVERKA = Worksheets("PROVERKA").Cells.SpecialCells(xlCellTypeLastCell).Row
For Each oCell In Worksheets("CENA_TOVARA").Range("A1:A" & LastRowCENA_TOVARA)
    Set oFindCell = Worksheets("PROVERKA").Range("A1:A" & LastRowPROVERKA). _
        Find(What:=oCell.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not oFindCell Is Nothing Then
            oCell.Offset(0, 5).Value = oFindCell.Offset(0, 5).Value
        End If
Next oCell
End Sub

Sub m_1()
Dim LastRowPROVERKA As Long
Dim LastRowCENA_TOVARA As Long
Dim oCell As Excel.Range
Dim oFindCell As Excel.Range
LastRowPROVERKA = Worksheets("PROVERKA").Cells.SpecialCells(xlCellTypeLastCell).Row
LastRowCENA_TOVARA = Worksheets("CENA_TOVARA").Cells.SpecialCells(xlCellTypeLastCell).Row
For Each oCell In Worksheets("PROVERKA").Range("A1:A" & LastRowPROVERKA)
    Set oFindCell = Worksheets("CENA_TOVARA").Range("A1:A" & LastRowCENA_TOVARA). _
        Find(What:=oCell.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If oFindCell Is Nothing Then
            Worksheets("PROVERKA").Range("A" & oCell.Row & ":F" & oCell.Row).Interior.Color = 65535
        End If
Next oCell
For Each oCell In Worksheets("PROVERKA").Range("E1:E" & LastRowPROVERKA)
    Set oFindCell = Worksheets("CENA_TOVARA").Range("E1:E" & LastRowCENA_TOVARA). _
        Find(What:=oCell.Value, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If oFindCell Is Nothing Then
            Worksheets("PROVERKA").Range("A" & oCell.Row & ":F" & oCell.Row).Interior.Color = 65535
        End If
Next oCell
End Sub

Sub m_1()
Dim LastRowPROVERKA As Long
Dim LastRowCENA_TOVARA As Long
Dim oFindYellow As Excel.Range
Dim oFindGRUPPA_PROVERKA As Excel.Range
Dim oFindGRUPPA_CENA_TOVARA As Excel.Range
Dim Address As String
Application.FindFormat.Interior.Color = 65535
LastRowPROVERKA = Worksheets("PROVERKA").Cells.SpecialCells(xlCellTypeLastCell).Row
With Worksheets("PROVERKA").Range("A1:A" & LastRowPROVERKA)
    Set oFindYellow = .Find(What:="", SearchFormat:=True)
    If Not oFindYellow Is Nothing Then
        Address = oFindYellow.Address
        Do
            LastRowCENA_TOVARA = Worksheets("CENA_TOVARA").Cells.SpecialCells(xlCellTypeLastCell).Row
            Worksheets("PROVERKA").Range("A" & oFindYellow.Row & ":F" & oFindYellow.Row).Copy
            Set oFindGRUPPA_PROVERKA = Worksheets("PROVERKA").Range("E1:E" & oFindYellow.Row) _
                .Find(What:="GRUPPA", LookIn:=xlValues, LookAt:=xlPart, _
                SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, _
                MatchCase:=False, SearchFormat:=False)
            Set oFindGRUPPA_CENA_TOVARA = Worksheets("CENA_TOVARA").Range("E1:E" & LastRowCENA_TOVARA) _
                .Find(What:=oFindGRUPPA_PROVERKA.Value, LookIn:=xlValues, LookAt:=xlWhole, _
                SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            oFindGRUPPA_CENA_TOVARA.Offset(1, 0).EntireRow.Insert
            Set oFindYellow = .Find(What:="", SearchFormat:=True, after:=oFindYellow)
        Loop While oFindYellow.Address <> Address And Not oFindYellow Is Nothing
    End If
End With
Application.CutCopyMode = False
Application.FindFormat.Clear
End Sub

Sub m_1()
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\" & "invoice_" & Worksheets("INVOICE").Range("D3") & "_" & Date & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
End Sub

Sub m_1()
Dim LastRowPROVERKA As Long
Dim LastRowCENA_TOVARA As Long
Dim oFindYellow As Excel.Range
Dim oFindGRUPPA_PROVERKA As Excel.Range
Dim oFindGRUPPA_CENA_TOVARA As Excel.Range
Dim Address As String
LastRowPROVERKA = Worksheets("PROVERKA").Cells.SpecialCells(xlCellTypeLastCell).Row
With Worksheets("PROVERKA").Range("A1:A" & LastRowPROVERKA)
    Application.FindFormat.Interior.Color = 65535
    Set oFindYellow = .Find(What:="", SearchFormat:=True)
    If Not oFindYellow Is Nothing Then
        Address = oFindYellow.Address
        Do
            LastRowCENA_TOVARA = Worksheets("CENA_TOVARA").Cells.SpecialCells(xlCellTypeLastCell).Row
            With Application.FindFormat.Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.249977111117893
                .PatternTintAndShade = 0
            End With
            Set oFindGRUPPA_PROVERKA = Worksheets("PROVERKA").Range("E1:E" & oFindYellow.Row) _
                .Find(What:="", SearchDirection:=xlPrevious, SearchFormat:=True)
            Set oFindGRUPPA_CENA_TOVARA = Worksheets("CENA_TOVARA").Range("E1:E" & LastRowCENA_TOVARA) _
                .Find(What:=oFindGRUPPA_PROVERKA.Value, LookIn:=xlValues, LookAt:=xlWhole, _
                SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            oFindGRUPPA_CENA_TOVARA.Offset(1, 0).EntireRow.Insert
            Worksheets("PROVERKA").Range("A" & oFindYellow.Row & ":F" & oFindYellow.Row).Copy
            Worksheets("CENA_TOVARA").Paste _
                Destination:=Worksheets("CENA_TOVARA").Range("A" & oFindGRUPPA_CENA_TOVARA.Row + 1)
            Application.FindFormat.Clear
            Application.FindFormat.Interior.Color = 65535
            Set oFindYellow = .Find(What:="", SearchFormat:=True, after:=oFindYellow)
        Loop While oFindYellow.Address <> Address And Not oFindYellow Is Nothing
    End If
End With
Application.CutCopyMode = False
Application.FindFormat.Clear
End Sub

Sub m_1()
Dim oFindГалочка As Excel.Range
Dim LastRowSKLAD As Long
Dim Address As String
Dim LastRowINVOICE As Long
LastRowINVOICE = 15
LastRowSKLAD = Worksheets("SKLAD").Cells.SpecialCells(xlCellTypeLastCell).Row
With Worksheets("SKLAD").Range("B1:B" & LastRowSKLAD)
    Set oFindГалочка = .Find(What:="a", LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not oFindГалочка Is Nothing Then
        Address = oFindГалочка.Address
        Do
            Range("A" & LastRowINVOICE).EntireRow.Insert
            With Worksheets("INVOICE")
                .Range("A" & LastRowINVOICE) = oFindГалочка.Offset(0, -1)
                .Range("B" & LastRowINVOICE) = oFindГалочка.Offset(0, 3)
                .Range("G" & LastRowINVOICE) = oFindГалочка.Offset(0, 2)
                '.Range("H" & LastRowINVOICE) = oFindГалочка.Offset(0, -1)
                .Range("I" & LastRowINVOICE) = oFindГалочка.Offset(0, 5)
                .Range("J" & LastRowINVOICE) = oFindГалочка.Offset(0, 6)
            End With
            Set oFindГалочка = .FindNext(oFindГалочка)
            LastRowINVOICE = LastRowINVOICE + 1
        Loop While oFindГалочка.Address <> Address And Not oFindГалочка Is Nothing
    End If
End With
End Sub

Sub m_1()
Dim oFindГалочка As Excel.Range
Dim LastRowSKLAD As Long
Dim Address As String
Dim LastRowINVOICE As Long
Dim SKLAD As Excel.Worksheet
Dim INVOICE As Excel.Worksheet
Set SKLAD = Workbooks("2010_CENNIK_RUS_1_2_WEB.xlsm").Worksheets("SKLAD")
Set INVOICE = Workbooks("2010_CENNIK_RUS_1_2_WEB.xlsm").Worksheets("INVOICE")
LastRowINVOICE = 15
LastRowSKLAD = SKLAD.Cells.SpecialCells(xlCellTypeLastCell).Row
With SKLAD.Range("B1:B" & LastRowSKLAD)
    Set oFindГалочка = .Find(What:="a", LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    If Not oFindГалочка Is Nothing Then
        Address = oFindГалочка.Address
        Do
            With INVOICE
                .Range("A" & LastRowINVOICE).EntireRow.Insert
                .Range("A" & LastRowINVOICE) = oFindГалочка.Offset(0, -1)
                .Range("B" & LastRowINVOICE) = oFindГалочка.Offset(0, 3)
                .Range("G" & LastRowINVOICE) = oFindГалочка.Offset(0, 2)
                '.Range("H" & LastRowINVOICE) = oFindГалочка.Offset(0, -1)
                .Range("I" & LastRowINVOICE) = oFindГалочка.Offset(0, 5)
                .Range("J" & LastRowINVOICE) = oFindГалочка.Offset(0, 6)
            End With
            Set oFindГалочка = .FindNext(oFindГалочка)
            LastRowINVOICE = LastRowINVOICE + 1
        Loop While oFindГалочка.Address <> Address And Not oFindГалочка Is Nothing
    End If
End With
End Sub