Selected Answer
Hi Leap
In the attached version of your workbook, I've expanded your code to that below (with comments).
Firstly I loop through Sheet1 to detect the rows not recorded in Sheet2 and add those to the end of sheet2. I set those additions to a red fill (using the line ending ...Interior.Color = vbRed but you can delete that if not needed). Note that I had some problems with your file so used Sheets(Sheets1") etc. a lot.
After that, your comparison code is run then I adjust columns D:F using a Select Case/ End Select (instead of mutilpe If statements) to add ticks and crosses and to move positive values to the surplus column:
Sub UpdateSheet2()
Dim LR1 As Long, LR2 As Long, n As Long
LR1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
LR2 = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
' First find (and add) any Brands which do not exist on Sheet 2
For Each Cll In Sheets("Sheet1").Range("A2:A" & LR1)
If Not IsNumeric(Application.Match(Cll.Value, Sheets("Sheet2").Columns(1), 0)) Then 'if no Exact Match...
LR2 = LR2 + 1
Cll.Resize(1, 2).Copy Sheets("Sheet2").Range("A" & LR2)
Sheets("Sheet2").Range("B" & LR2).Value = "" 'set to blank
Sheets("Sheet2").Range("A" & LR2).Resize(1, 5).Interior.Color = vbRed
End If
Next Cll
' Compare the values for Sheet1 and Sheet 2
With Sheets("Sheet2")
With .Range("D2:D" & LR2)
.Formula = "=IF(ISNUMBER(MATCH(A2, Sheet1!A:A, 0)), B2 - VLOOKUP(A2, Sheet1!A:B, 2, 0), B2)"
.Value = .Value
End With
'set condition font (to include new rows)
.Range("C2:C" & LR2).Font.Name = "Wingdings"
' adjust columns D:E
For n = 2 To LR2
Select Case .Range("D" & n)
Case 0
.Range("C" & n).Value = "ü" ' tick if no difference
Case Is < 0
.Range("C" & n).Value = "û" 'cross if different
Case Is > 0
.Range("C" & n).Value = "û" 'cross if different
.Range("E" & n).Value = .Range("D" & n).Value 'move value
.Range("D" & n).Value = ""
End Select
Next n
End With
End Sub
Hope this is what you need.
