Not Repeating An Entry

J

John13

I have 13 columns of information and column A is Order Number. What is
the best way to not enter the same order number twice, short of doing a
Find prior to entering it? I was trying to do a conditional format
that if it equalled and other cell in column A, turn the cell
background Red. Thanking you in advance your help.

John
 
D

Dave Peterson

You could use Format|Conditional formatting to highlight the duplicates:
(with a custom formula of)
=countif(a:a,a1)>1

Or you could use Data|Validation
and a custom formula of:
=COUNTIF($A$1:A1,A1)<2
(with A1 the active cell)
 
D

Don Guillett

right click sheet tab>view code insert this.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set mr = Range(Cells(1, 1), Cells(lr, 1))
On Error GoTo leaveit
If mr.Find(Target) Then MsgBox "Pick Another Number"
leaveit:
Application.EnableEvents = True
End Sub
Sub fixit()
Application.EnableEvents = True
End Sub
 
J

John13

Dave,

Thank you, the Conditional Formatting worked great. I couldn't get the
Data|Validation to work and was wondering if you could walk me through
the steps. Thank you.

John13
 
D

Dave Peterson

My data was in A1:A10 (say)
I selected A1:A10 and with a1 the activecell
I did data|validation
On the settings tab
Allow: Custom
Formula: =COUNTIF($A$1:A1,A1)<2

and finished up the other tabs

Then if I typed: ASDF in A1
and tried to type ASDF in A2:A10, I'd get the warning.
 
Top