compare numbers and mark duplicates

[Lucas]

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

[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


"Lucas" <Lucas@discussions.microsoft.com> wrote in message
news:1A688E7C-AF21-4124-A954-AE3C89877F5A@microsoft.com...
> 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

[bj]

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

[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" <Lucas@discussions.microsoft.com> wrote in message
> news:1A688E7C-AF21-4124-A954-AE3C89877F5A@microsoft.com...
> > 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
>
>
>

[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


"Lucas" <Lucas@discussions.microsoft.com> wrote in message
news:ED46245C-C8C2-4F59-8528-24E377F3203E@microsoft.com...
> 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" <Lucas@discussions.microsoft.com> wrote in message
>> news:1A688E7C-AF21-4124-A954-AE3C89877F5A@microsoft.com...
>> > 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
>>
>>
>>

[bj]

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" <Lucas@discussions.microsoft.com> wrote in message
> > news:1A688E7C-AF21-4124-A954-AE3C89877F5A@microsoft.com...
> > > 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
> >
> >
> >