VBA to import/update range between workbooks (incl. different columns for results)

Question

I am seeking help on below task and would be glad for help!

What I am trying to achieve:

1. Get data from the exporting sheet (starting at B8 until the last row in B) and compare it to the data in the importing sheet (starting at C12)
2. If data does not exist below line C12, add it below (if any) the existing items ; else if data exists somewhere between C12 and the last item in column C, update it
3. Add or update only the columns B to D from the exporting sheet.
4. Add or update column D from the exporting sheet in column F of the importing sheet.
5. Lastly (end game), change price cell background color of updated items to green, of pre-existing items not in the exporting sheet to red, and of newly added items to blue.

I took the code found Here to start with but after heavy modification to achieve above tasks, it stopped working halfway through my lists of things to do.

Below is the code (no error handlers etc. yet) and further down a picture (what I want to achieve should look like ; red background color for cells that should not be added or updated from one sheet to the other). Also please note that I dim so many variables because I want to implement different choices later.

Again, any help would be highly appreciated

UPDATE: FINAL CODE FOR ANYONE INTERESTED

• Adds a new row for each new item
• Ignores empty "in-between" rows in both sheets

• Price row is color coded as described above

  Option Explicit  
  Sub Import()
  
  Const IMPORTFILENAME = "export_data.xlsx"
  Dim key As Variant
  Dim cell As Range
  Dim dProducts As Object
  Set dProducts = CreateObject("Scripting.Dictionary")
  
  With Workbooks(IMPORTFILENAME).Sheets(1)
  For Each cell In .Range("B8", .Range("B" & .Rows.Count).End(xlUp))
   If Not IsEmpty(cell) Then
      key = cell.Value
      If dProducts.Exists(key) Then
          'There is a duplicate value
          Debug.Print "Duplicate values", dProducts(key).Address, cell.Address
      Else
          'Add the cell range object to the dictionary
          dProducts.Add key, cell
      End If
    Else
    End If
  Next
  End With
  
  With ThisWorkbook.Sheets(1)
  For Each cell In .Range("C12", .Range("C" & .Rows.Count).End(xlUp))
      key = cell.Value
      If dProducts.Exists(key) Then
          cell.Offset(0, 1).Value = dProducts(key).Offset(0, 1).Value
          cell.Offset(0, 3).Value = dProducts(key).Offset(0, 2).Value
          cell.Offset(0, 3).Interior.Color = vbGreen
          'Remove the Export cell reference
          dProducts.Remove key
      Else
        If Not IsEmpty(cell) Then
          If cell.Value <> 0 Then
              cell.Offset(0, 3).Interior.Color = vbRed
          Else
          End If
        Else
        End If
      End If
  Next
  
  For Each key In dProducts.Keys
      With .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0)
          .EntireRow.Insert
          .Offset(-1, 0).Value = dProducts(key).Value
          .Offset(-1, 3).Interior.Color = vbBlue
          .Offset(-1, 1).Value = dProducts(key).Offset(0, 1).Value
          .Offset(-1, 3).Value = dProducts(key).Offset(0, 2).Value
      End With
  Next
  End With
  End Sub
  

PICTURE

Answer 1

Your code seems to have the two sheets backwards. In any case, I based my code on your image. I'm not sure of some of the formatting that you wanted, but you should easily be able to modify the code to suite your needs.

I recommend using a Dictionary or collections when comparing unique items in two list (or searching for duplicates). You should watch: Excel VBA Introduction Part 39 - Dictionaries

In many cases you could use constant to reduce the lines of code. I also recommend short variable names and the use of With blocks to reduce code clutter. Ideal you should never have to scroll horizontally to see what a line of code is doing.

Sub Import2()
    Const IMPORTFILENAME = "XXX.xlsx"
    Dim key As Variant
    Dim cell As Range
    Dim dProducts As Object
    Set dProducts = CreateObject("Scripting.Dictionary")

    With ThisWorkbook.Sheets(1)
        For Each cell In .Range("B8", .Range("B" & .Rows.Count).End(xlUp))
            key = cell.Value
            If dProducts.Exists(key) Then
                'There is a duplicate value
                Debug.Print "Duplicate values", dProducts(key).Address, cell.Address
            Else
                'Add a the cell range object to the dictionary
                dProducts.Add key, cell
            End If
        Next
    End With

    With Workbooks(IMPORTFILENAME).Sheets(1)
        For Each cell In .Range("C12", .Range("C" & .Rows.Count).End(xlUp))
            key = cell.Value
            If dProducts.Exists(key) Then
                cell.Offset(0, 1).Value = dProducts(key).Offset(0, 1).Value
                cell.Offset(0, 3).Value = dProducts(key).Offset(0, 2).Value
                cell.Interior.Color = vbRed
                'Remove the Export cell reference
                dProducts.Remove key
            End If
        Next

        For Each key In dProducts.Keys
            With .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0)
                .Value = dProducts(key).Value
                .Offset(0, 1).Value = dProducts(key).Offset(0, 1).Value
                .Offset(0, 3).Value = dProducts(key).Offset(0, 2).Value
            End With
        Next
    End With
End Sub