VBA code to Highlight duplicate columns in excel

Question

I am working on a VBA to validate the contents of an excel sheet. I want unique values in the first column, and to be able to determine the validity of these values using the foreign keys on another column. This is what I have to validate for unique entries:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.CountIf(Range("A:A"), Target) > 1 Then
        MsgBox "Duplicate Data", vbCritical, "Remove Data"
        Target.Value = ""
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

It prevents duplicate entry in the first row. But what I really want is to be able to detect duplicate by running the macros on an already filled spreadsheet, and to have invalid fields highlighted.

Answer 1

This should do the trick:

Sub sbHighlightDuplicatesInColumn()
Dim lastCol As Long
Dim matchFoundIndex As Long
Dim iCntr As Long

lastCol = Sheets("Sheet1").Range("A1").SpecialCells(xlCellTypeLastCell).Column
For iCntr = 1 To lastCol
    If Cells(1, iCntr) <> "" Then
        matchFoundIndex = WorksheetFunction.Match(Cells(1, iCntr), Range(Cells(1, 1), Cells(1, iCntr)), 0)
        If iCntr <> matchFoundIndex Then
            Sheets("Sheet1").Cells(1, iCntr).Interior.Color = vbYellow
        End If
    End If
Next
End Sub