Compare 2 cells in different sheets in VBA(Excel 2010)

Question

Hi Can I ask for a sample macro code to compare 2 different columns from 2 different sheets.

Here's the columnA in sheet1

Here's the column A in sheet2

Here's what I need to make as an output in sheet1

Then all cells in column A sheet1 without match such as red in the picture above should be cut and copied in column C in sheet1 like the below

lastly all cells in column A sheet 2 that has no match should be cut as well and pasted in column D in sheet 1 such as ABC:PINK, ABC:VIOLET and ABC:BLACK as shown below

Thanks for the help in advance.

Here's what I got so far

Sub Button1_Click()
On Error GoTo ErrorHndler:
Dim myRange As Range
Dim sRng As Range

Set myRange = Range("A1:A50")

Start:
     For Each sRng In myRange
       If sRng Like Sheets("Sheet2").Range("A1").Value Then
          MsgBox (Sheets("Sheet2").Range("A1").Value) <----it does not pass here 
          (----I have no Idea what to put here-----)
          'GoTo NextCell
       Else
          'GoTo Start
          MsgBox (Sheets("Sheet2").Range("A1").Value)
          'MsgBox "Doesn't match"  <-----for debugging purposes
       End If
 NextCell:
 Next sRng

 ErrorHandler:
 MsgBox ""
 End Sub

Answer 1

You can search a range for a value using Range.Find

Range.Find returns Nothing if no match is found or a Range if a match is found.

You can check if two objects refer to the same object using the is operator.

Here is an example:

Sub lookup()
    Dim TotalRows As Long
    Dim rng As Range
    Dim i As Long

    'Copy lookup values from sheet1 to sheet3
    Sheets("Sheet1").Select
    TotalRows = ActiveSheet.UsedRange.Rows.Count
    Range("A1:A" & TotalRows).Copy Destination:=Sheets("Sheet3").Range("A1")

    'Go to the destination sheet
    Sheets("Sheet3").Select

    For i = 1 To TotalRows
        'Search for the value on sheet2
        Set rng = Sheets("Sheet2").UsedRange.Find(Cells(i, 1).Value)
        'If it is found put its value on the destination sheet
        If Not rng Is Nothing Then
            Cells(i, 2).Value = rng.Value
        End If
    Next
End Sub