Buscar datos duplicados de una columna con macros

Te mando mi solución:

En mi ejemplo estoy analizando la columna D desde D1 hasta donde termine. La macro te pintará de rojo los valores repetidos y te informará de cual es el último valor y su dirección.

Sub prueba()
'por luismondelo
ultimo = Range("d65000").End(xlUp).Value
dire = Range("d65000").End(xlUp).Address
For Each celda In Range("d1:" & dire)
If Application.WorksheetFunction.CountIf(Columns(celda.Column), celda.Value) > 1 Then
celda.Select
celda.Interior.ColorIndex = 3
End If
Next
MsgBox "el último valor es: " & ultimo & Chr(13) & "y está en la celda: " & dire
End Sub

no olvides finalizar la consulta