compare numbers and mark duplicates
 

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

try the countif function in your conditional formating

"Lucas" wrote:

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

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


"Lucas" wrote in message
...
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

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" wrote:

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


"Lucas" wrote in message
...
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

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


"Lucas" wrote in message
...
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" wrote:

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


"Lucas" wrote in message
...
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

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.

"Lucas" wrote:

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" wrote:

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


"Lucas" wrote in message
...
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