want to find duplicate Phone numbers when typed in excel 2003

  • Thread starter Thread starter trvlpal
  • Start date Start date
T

trvlpal

I create a column and when i put a phone number in I want to have it
indivcate if its a duplicate perferrable in red
 
Let's assume this is your data:

A1: 12345
A2: 54321
A3: 12345

Which cell(s) should be highlighted?
 
Right click sheet tab>view code>insert this>change column ltr to suit and
color index to suit.

Private Sub Worksheet_Change _
(ByVal Target As Range)
If Intersect(Target, Columns("M")) _
Is Nothing Then Exit Sub
If Application.CountIf(Columns("m"), _
Target) > 1 Then
MsgBox "Duplicate"
Target.Interior.ColorIndex = 6
End If
End Sub
 
Select the whole column (say it's col A), use Format/Conditional Formatting,
change "Cell Value Is" to "Formula Is", enter this formula:
=COUNTIF($A$1:$A$1000,A1)>1
click Format, then Patterns tab, then select the red font.
HTH
Bob Umlas
Excel MVP
 
Back
Top