Solved: Highlight Duplicates

[tqm1]

Dear Experts

Column A has following numbers

1
2
4
4
1
5
6
2
8
1

I want to highlight all duplicate numbers.
Please help

[sujittalukde]

Use conditional formatting.

[frsm]

you can use this formula in the conditional format

=COUNTIF($A$1:$A$9;$A1)>1

[tqm1]

I want to use command button to highlight duplicate cells.
So what will be the codes behind command button

[frsm]

may be this code will help you



Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1:A100").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=countif($A$1:$A$100;a1)>1"
Selection.FormatConditions(1).Font.ColorIndex = 3
End Sub

[tqm1]

Your codes show

Run-time error 5
Invalid procedure call or argument

may there is error in this line
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=countif($A$1:$A$100;a1)>1"

Please help again

[frsm]

replace this please
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=countif($A$1:$A$100,a1)>1"

[tqm1]

My problem still not solved

[Charlize]

In which language is your excel application. If it's not English, you'll have to change countif to the keyword in your language.

[tqm1]

Dear Sir,

I am using English version of Microsoft Office.

The above codes still displaying this error message

Run-time error 5
Invalid procedure call or argument

What is wrong?

[Charlize]

Post your example workbook so we could see the error.

[rbrhodes]

tqm1,

The requested change was subtle are you sure you got it?

$A$100 ; a1 becomes $A$100 , a1

This version allows you to set a range - See line CHANGE TO SUIT. Simply change "$A$1:$A$100" to whatever range you want, (keep the "").


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rng As Range
Dim goHome As String

'CHANGE TO SUIT
Set rng = Range("A1:A100")

'speed
Application.ScreenUpdating = False

'come back here
goHome = ActiveCell.Address

'Can't be done without select!
rng.Select

With Selection
'kill old
.FormatConditions.Delete
'put formula
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=countif(" & rng.Address & "," & rng.Address(0, 0) & ") >1"
'put format
.FormatConditions(1).Interior.ColorIndex = 3
End With

'return
Range(goHome).Select

'cleanup
Set rng = Nothing

'reset
Application.ScreenUpdating = True

End Sub

[vunhai]

Dear rbrhodes!

Your code very helpful for me, i have one question, if in " A1: A100" have more than 2 duplicates value how can we highlight more than color for each difference duplicates

Thank you very much in advance