Highlighting all duplicates except the last instance

Hi All, I have a problem and this is beyond my ability.

I have a sheet of data that is never sorted or filtered, and I need to only use the last instance of any duplicate, what id like to do is highlighted all previous duplicates but NOT the last instance.

Im sure this is possible but I have no idea where to start, any help would be appriciated.

Hi,

I assumed the data to look for is in column A, try this:

Option Explicit

Sub bopsgtir()
    Const hColor = 52479
    Dim coll As New Collection
    Dim xlRng As Range, s1stAddr As String, sLastAddr As String
    Dim i As Long, lCount As Long
    
    With ActiveSheet
    
        i = 2
        Do Until .Cells(i, 1).Value = vbNullString 'get uniques
            On Error Resume Next
            coll.Add CStr(.Cells(i, 1).Value), CStr(.Cells(i, 1).Value)
            On Error GoTo 0
            i = i + 1
        Loop
        
        For i = 1 To coll.Count
            Set xlRng = .Columns(1).Find(What:=coll(i), LookIn:=xlValues, lookat:=xlWhole)
            If Not xlRng Is Nothing Then
                s1stAddr = xlRng.Address
                lCount = 1
                Do
                    If lCount > 1 Then
                        xlRng.Interior.Color = hColor
                    End If
                    Set xlRng = .Columns(1).FindNext(xlRng)
                    If s1stAddr <> xlRng.Address And Not xlRng Is Nothing Then
                        sLastAddr = xlRng.Address
                        lCount = lCount + 1
                    End If
                Loop Until s1stAddr = xlRng.Address Or xlRng Is Nothing
                If lCount > 1 Then
                    .Range(sLastAddr).Interior.Color = xlNone
                    .Range(s1stAddr).Interior.Color = hColor
                End If
            End If
        Next i
    End With
End Sub

Regards