Duplicate Values

Highlight - Duplicate Values in Single Column

Use FIND function

Private Sub CommandButton1_Click()
Dim rng As Range
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
Dim target As Range
For i = 1 To rng.Rows.Count
Set target = rng.Find(what:="apple", Lookat:=xlWhole)
If Cells(i, 1).Value = target Then
Cells(i, 1).Interior.ColorIndex = 7
End If
Next
End Sub

Highlight - Duplicate Values in Second Column

Private Sub CommandButton1_Click()
Dim first As Range
Set first = Range(Range("A1"), Range("A1").End(xlDown))
Dim j As Range
Dim second As Range
Set second = Range(Range("C1"), Range("C1").End(xlDown))
For i = 1 To second.Rows.Count
Set j = first.Cells.Find(what:=Cells(i, 3).Value, lookat:=xlWhole)
If Not j Is Nothing Then
Cells(i, 3).Interior.ColorIndex = 7
End If
Next
End Sub

Remove Duplicates in Single Column

Private Sub CommandButton1_Click()
Dim first As Range
'Set first = Range("A1:A50")
Set first = UsedRange.Rows
Dim i As Integer
For i = 1 To first.Count
If WorksheetFunction.CountIf(first, Cells(i, 1).Value) > 1 Then
Cells(i, 1).ClearContents
End If
Next
Columns(1).SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub

Remove Duplicates - in Second column

Private Sub CommandButton1_Click()
Dim first As Range, second As Integer
Set first = Range("A1:A50")
second = Range(Range("B1"), Range("B1").End(xlDown)).Rows.Count
Dim i As Integer
For i = 1 To second
If WorksheetFunction.CountIf(first, Cells(i, 2).Value) > 0 Then
Cells(i, 2).ClearContents
End If
Next
Columns(2).SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub

Remove Duplicates & Blank Cells - Result in Second column

Private Sub CommandButton1_Click()
UsedRange.Copy Range("B1")
Dim last As Integer
last = Range("B" & Rows.Count).End(xlUp).Row
Dim Data As Range
Set Data = Range(Range("B1"), Range("B" & last)).Rows
For i = 1 To last
If WorksheetFunction.CountIf(Data, Cells(i, 2).Value) > 1 Then
Cells(i, 2).ClearContents
End If
Next
Data.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub

Hightlight Duplicates - Range

through WHAT FUNCTION we can cross check the cell value

Private Sub CommandButton1_Click()
Dim fc As Range
Set fc = Range(Range("A1"), Range("A1").End(xlDown))
Dim j As Range
Dim sc As Range
Set sc = Range(Range("C1"), Range("C1").End(xlDown))
For i = 1 To sc.Rows.Count
Set j = fc.Cells.Find(what:=Cells(i, 3).Value, lookat:=xlWhole)
If Not j Is Nothing Then
Cells(i, 3).Interior.ColorIndex = 7
End If
Next
End Sub

Remove Duplicates

Private Sub CommandButton1_Click()
Range("A2:A25").RemoveDuplicates Columns:=Array(1), Header:=xlNo
End Sub