Highlight Duplicate Values

[jskushawah]

I have some values in range A1:A34 between 1,25.

I need a formula which highlights the duplicate values.


Any help will appreciate.

 

[amrit1186]

Hi Jitendra


Use the following formula in Conditional formatting.


=COUNTIF($A$1:$A$34,A1)>1


Amritansh

 

[NARAYANK991]

Hi ,


This is not related to the original poster's question , but just in case anyone finds it useful.

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.Intersect(Target, Range("Data_Range")) Is Nothing Then
Range("Data_Range").Interior.Color = xlNone
Exit Sub
End If

Dim duplicates As Boolean

Dim dr As Range
Set dr = Range("Data_Range")

Dim min_row As Long
min_row = dr.Row

Dim data_array As Variant
data_array = dr.Value
dr.Interior.Color = xlNone
row_num = Target.Row - min_row + 1
For i = LBound(data_array, 1) To UBound(data_array, 1)
If data_array(i, 1) = Target And row_num <> i Then
duplicates = True
dr.Cells(1, 1).Offset(i - 1).Interior.Color = vbYellow
End If
Next

If duplicates Then
Target.Interior.Color = vbYellow
Else
MsgBox "No duplicates for this data ..."
End If
End Sub

[/pre]
Narayan

 

[Excel_Kid_663]

You could always use conditional formatting too which will colour a cell based on duplicate values

 

[jskushawah]

Thanks to amrit1186, NARAYANK991, Excel_Kid_663