compare numbers and mark duplicates

[Guest]

Hello all,

I have a row of 9 cells, which I want to fill with the numbers 1 to 9. Each
time I put a number in a cell I want excel to compare all 9 cells in the row.
If I type in a number that is already in one of the other 8 cells I want it
to light up in red. I tried using "conditional formatting" but I can only
compare 2 cells at a time. Does anybody have an idea?

Thanks in advance,
Lucas

 

[Guest]

try the countif function in your conditional formating

 

[Damon Longworth]

Try something similar to this conditional formatting - Formula Is:

=COUNTIF($D$1:$D$8,D1)>1

D1 must be the active cell and note the absolute and relative references.

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com

 

[Guest]

the COUNTIF function doesnt work with a variable criteria, it only seem to
get it to work when I use things like ">55" or "=3", but not for example cell
"D7"...

Lucas

 

[Damon Longworth]

Did you try putting a number in $E$1 and then using this in the countif?
For example, place 3 in E1 and then your countif:

=COUNTIF($D$1:$D$8,D1)>$E$1

--
Damon Longworth

Don't miss out on the 2005 Excel User Conference
Sept 16th and 17th
Stockyards Hotel - Ft. Worth, Texas
www.ExcelUserConference.com

 

[Guest]

From the way yo wrote it, are you using the quote marks around the cell
reference in the countif equation?
if you are referencing a cell do not use the quote marks.