highlighting cells if value is repeated

[Skills]

Hi

I have a column named item number, the user enters a number into thes
cells, I would like a cell to be highlighted red if the same number i
used again as well as the orignal cell. What feature would I use to d
this?

Thanks for any help

 

[Bob Phillips]

conditional formatting, with a formula of

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

 

[Tom Ogilvy]

use conditional formatting for this

You would use the countif worksheet function to determine if there are
duplicates.

 

[Ken Wright]

Assume your values are all in Col A, select Col A, do Format / Conditional
Formatting / Change 'Cell value' is to 'Formula is' and put in
=AND(A1<>"",COUNTIF($A:$A,A1)>1) and then hit the format button and choose a red
pattern from the pattern tab.

If data was across Columns A:C then select columns A:C and change formula to:-

=AND(A1<>"",COUNTIF($A:$C,A1)>1)

If only using a small range then select only that range and ensure that the top
left cell in your range when selected is what goes into the formula where A1
currently is, and the whole range replaces the $A:$C bit.

 

[Gord Dibben]

Skills

See Chip Perason's "Highlighting Duplicates" info.

http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

See also his "Preventing Duplicates" if that is your goal.

http://www.cpearson.com/excel/NoDupEntry.htm

Gord Dibben Excel MVP