VBA checking for duplicates in a column

[atblack22]

I have a sheet setup in excel and in column A I have values listed down an undetermined number of rows and there are blank rows inbetween the cells that have data. I need to develop a VBA code that will check for duplicates in this entire column.

 

[atblack22]

I need it to identify duplicates, but not remove them. or if thats not possible, just to say there are duplicates, and review.

 

[chuckles1066]

Wouldn't you just type

 =IF(COUNTIF($A$1:$A$65000,A1)>1,"DUPLICATE","")

into B1 and drag down?

 

[atblack22]

I could, but I don't want to add that whole extra column of data in for users. I currently have this formula in cell F2 = =IF(COUNTA(A:A)=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&"")),"No Duplicates","Duplicates")

Then I have a button that executes the next step in the process and it checks the cell value and if its duplicate it stops the process, and if its No Duplicate, then it moves to the next step, but I have a few more subprocesses within that macro that with this execution, it makes the cell fromula recalculate each time and slows down processing, so i want to eliminate.

 

[Maheshp]

Hi
Try :

Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value = Range("A" & i - 1).Value Then Range("A" & i).Font.ColorIndex = 3
Next i
End Sub