Macro to identify missing value and include in range

Hi guys,

I hope one of you gents might be able to help with this small matter.

If in Sheet1 column A I have the following entries:


Black
Blue
Brown
Yellow


And in Sheet2 Column B I have


Blue
Brown

I wanted a macro that would identify which values are in Sheet1 Column A, but not in Sheet 2 Column B (Black and Yellow), and include them in Column B, one cell after the last entry.

I posted the question in another forum and got a code that would work only of the columns were in the same sheet. Maybe one of you guys might work on that and help me out!

Thanks in advance!

Sub SearchData() 
    Dim MyRgA As Range, MyRgB As Range 
    Dim F  As Range 
    Dim LastRow As Long 
     
    LastRow = Range("B" & Rows.Count).End(xlUp).Row 
    Set MyRgA = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp)) 
    Set MyRgB = Range("B:B") 
    For Each F In MyRgA 
         
        If (Application.WorksheetFunction.CountIf(MyRgB, F.Value) = 0) Then 
            LastRow = LastRow + 1 
            Cells(LastRow + 1, "B") = F.Value 
        End If 
    Next F 
End Sub