EXCEL: Highlight duplicate records from different spreadsheets in the same workbook

[jerelp]

I need to make a macro that finds duplicate records in Column A in sheet1 and Column A in sheet2. If there is a duplicate I need both to be highlighted in red. If there is no duplicate in Column A sheet1 then color that record green. If there is no duplicate in Column A sheet2 color that record yellow.

the sample data is:
Sheet1
CONTAINER
ABC123
ABC234
ABC345
ABC456

Sheet2
CONTAINER
ABC234
DFC123
ABC456
AET432

as you can see some of the data matches and some don't

Please help never made a macro in Excel before.

[jerelp]

I have code for a single column on one spreadsheet but, how can i manipulate it to do what I need it to do

Sub TestForDups()

    Dim LLoop As Integer
    Dim LTestLoop As Integer
    Dim LClearRange As String

    Dim Lrows As Integer
    Dim LRange As String
    Dim LChangedValue As String
    Dim LTestValue As String

    'Test first 200 rows in spreadsheet for uniqueness
    Lrows = 500
    LLoop = 2

    'Clear all flags
    LClearRange = "A2:A" & Lrows
    Range(LClearRange).Interior.ColorIndex = xlNone

    'Check first 500 rows in spreadsheet
    While LLoop <= Lrows
        LChangedValue = "A" & CStr(LLoop)

        If Len(Range(LChangedValue).Value) > 0 Then

            'Test each value for uniqueness
            LTestLoop = 2
            While LTestLoop <= Lrows
                If LLoop <> LTestLoop Then
                    LTestValue = "A" & CStr(LTestLoop)
                    'Value has been duplicated in another cell
                    If Range(LChangedValue).Value = Range(LTestValue).Value Then
                        'Set the background color to red
                        Range(LChangedValue).Interior.ColorIndex = 3
                        Range(LTestValue).Interior.ColorIndex = 3

                    End If

                End If

                LTestLoop = LTestLoop + 1

            Wend

        End If

        LLoop = LLoop + 1
    Wend

End Sub