highlight duplicate numbers

If I enter a number into any cell which has already been entered into another
cell, regardless of row or column, is there any way in which my attention
could be drawn to the fact that this number is already in the worksheet, i.e.
by highlighting or something similar.

Conditional formatting will do it with a formula of say

=COUNTIF(A:IV,A1)>1

--
HTH

Bob Phillips

"css" <[email protected]> wrote in message
news:[email protected]...
> If I enter a number into any cell which has already been entered into
another
> cell, regardless of row or column, is there any way in which my attention
> could be drawn to the fact that this number is already in the worksheet,
i.e.
> by highlighting or something similar.

BRILLIANT - 2 days of trying to figure it out and you got the solution just
like that. May thanks!!!!!!!!!!!!

"Bob Phillips" wrote:

> Conditional formatting will do it with a formula of say
>
> =COUNTIF(A:IV,A1)>1
>
> --
> HTH
>
> Bob Phillips
>
> "css" <[email protected]> wrote in message
> news:[email protected]...
> > If I enter a number into any cell which has already been entered into
> another
> > cell, regardless of row or column, is there any way in which my attention
> > could be drawn to the fact that this number is already in the worksheet,
> i.e.
> > by highlighting or something similar.
>
>
>

I have run into a small problem. The conditional formatting was working fine,
but I can only have 3 conditional formats. I will be entering many different
jobs numbers and will want all of them to have a conditional format so that
if I enter the same job number somewhere else within the same worksheet, it
will be highlighted or the font will change to bold. Is it possible to
conditional format so many different numbers?

"Bob Phillips" wrote:

> Conditional formatting will do it with a formula of say
>
> =COUNTIF(A:IV,A1)>1
>
> --
> HTH
>
> Bob Phillips
>
> "css" <[email protected]> wrote in message
> news:[email protected]...
> > If I enter a number into any cell which has already been entered into
> another
> > cell, regardless of row or column, is there any way in which my attention
> > could be drawn to the fact that this number is already in the worksheet,
> i.e.
> > by highlighting or something similar.
>
>
>

Are you up for an add-in?

Public Function MultiCat(ByRef rRng As Range, _
Optional ByVal sDelim As String = "") As String
Dim rCell As Range
For Each rCell In rRng
If rCell.Value <> "" Then
MultiCat = MultiCat & sDelim & rCell.Text
End If
Next rCell
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function


--
HTH

Bob Phillips

"css" <[email protected]> wrote in message
news:[email protected]...
> I have run into a small problem. The conditional formatting was working
fine,
> but I can only have 3 conditional formats. I will be entering many
different
> jobs numbers and will want all of them to have a conditional format so
that
> if I enter the same job number somewhere else within the same worksheet,
it
> will be highlighted or the font will change to bold. Is it possible to
> conditional format so many different numbers?
>
> "Bob Phillips" wrote:
>
> > Conditional formatting will do it with a formula of say
> >
> > =COUNTIF(A:IV,A1)>1
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "css" <[email protected]> wrote in message
> > news:[email protected]...
> > > If I enter a number into any cell which has already been entered into
> > another
> > > cell, regardless of row or column, is there any way in which my
attention
> > > could be drawn to the fact that this number is already in the
worksheet,
> > i.e.
> > > by highlighting or something similar.
> >
> >
> >

Don't know what happened here. It should read ...

Are you up for an add-in?

http://www.xldynamic.com/source/xld.....Download.html

--
HTH

Bob Phillips

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Are you up for an add-in?
>
> Public Function MultiCat(ByRef rRng As Range, _
> Optional ByVal sDelim As String = "") As String
> Dim rCell As Range
> For Each rCell In rRng
> If rCell.Value <> "" Then
> MultiCat = MultiCat & sDelim & rCell.Text
> End If
> Next rCell
> MultiCat = Mid(MultiCat, Len(sDelim) + 1)
> End Function
>
>
> --
> HTH
>
> Bob Phillips
>
> "css" <[email protected]> wrote in message
> news:[email protected]...
> > I have run into a small problem. The conditional formatting was working
> fine,
> > but I can only have 3 conditional formats. I will be entering many
> different
> > jobs numbers and will want all of them to have a conditional format so
> that
> > if I enter the same job number somewhere else within the same worksheet,
> it
> > will be highlighted or the font will change to bold. Is it possible to
> > conditional format so many different numbers?
> >
> > "Bob Phillips" wrote:
> >
> > > Conditional formatting will do it with a formula of say
> > >
> > > =COUNTIF(A:IV,A1)>1
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "css" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > If I enter a number into any cell which has already been entered
into
> > > another
> > > > cell, regardless of row or column, is there any way in which my
> attention
> > > > could be drawn to the fact that this number is already in the
> worksheet,
> > > i.e.
> > > > by highlighting or something similar.
> > >
> > >
> > >
>
>

This looks great. I will add that as it is something I will deffinatley use.
However, for this purpose, 30 conditional formats still isn't enough (I know,
I'm greedy!). I need to keep a record of all job numbers for a year so there
will be hundreds of jobs numbers. Rather than setting a conditional format
for a specific number, is there any way of creating a general rule that
should 'any' number be entered more than once in the same spreadsheet, it
will alert me in some way either by a message flashing up or simply change
the font colour etc.

"Bob Phillips" wrote:

> Don't know what happened here. It should read ...
>
> Are you up for an add-in?
>
> http://www.xldynamic.com/source/xld.....Download.html
>
> --
> HTH
>
> Bob Phillips
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Are you up for an add-in?
> >
> > Public Function MultiCat(ByRef rRng As Range, _
> > Optional ByVal sDelim As String = "") As String
> > Dim rCell As Range
> > For Each rCell In rRng
> > If rCell.Value <> "" Then
> > MultiCat = MultiCat & sDelim & rCell.Text
> > End If
> > Next rCell
> > MultiCat = Mid(MultiCat, Len(sDelim) + 1)
> > End Function
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "css" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have run into a small problem. The conditional formatting was working
> > fine,
> > > but I can only have 3 conditional formats. I will be entering many
> > different
> > > jobs numbers and will want all of them to have a conditional format so
> > that
> > > if I enter the same job number somewhere else within the same worksheet,
> > it
> > > will be highlighted or the font will change to bold. Is it possible to
> > > conditional format so many different numbers?
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > Conditional formatting will do it with a formula of say
> > > >
> > > > =COUNTIF(A:IV,A1)>1
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "css" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > If I enter a number into any cell which has already been entered
> into
> > > > another
> > > > > cell, regardless of row or column, is there any way in which my
> > attention
> > > > > could be drawn to the fact that this number is already in the
> > worksheet,
> > > > i.e.
> > > > > by highlighting or something similar.
> > > >
> > > >
> > > >
> >
> >
>
>
>

Are you saying that if anything gets duplicated then you want it to be
coloured, with a different colour for every different value duplicated?
There are only 56 colours in the colour palette, and many of them are very
similar, you would never spot the difference.

--
HTH

Bob Phillips

"css" <[email protected]> wrote in message
news:[email protected]...
> This looks great. I will add that as it is something I will deffinatley
use.
> However, for this purpose, 30 conditional formats still isn't enough (I
know,
> I'm greedy!). I need to keep a record of all job numbers for a year so
there
> will be hundreds of jobs numbers. Rather than setting a conditional format
> for a specific number, is there any way of creating a general rule that
> should 'any' number be entered more than once in the same spreadsheet, it
> will alert me in some way either by a message flashing up or simply change
> the font colour etc.
>
> "Bob Phillips" wrote:
>
> > Don't know what happened here. It should read ...
> >
> > Are you up for an add-in?
> >
> > http://www.xldynamic.com/source/xld.....Download.html
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:%[email protected]...
> > > Are you up for an add-in?
> > >
> > > Public Function MultiCat(ByRef rRng As Range, _
> > > Optional ByVal sDelim As String = "") As String
> > > Dim rCell As Range
> > > For Each rCell In rRng
> > > If rCell.Value <> "" Then
> > > MultiCat = MultiCat & sDelim & rCell.Text
> > > End If
> > > Next rCell
> > > MultiCat = Mid(MultiCat, Len(sDelim) + 1)
> > > End Function
> > >
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "css" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have run into a small problem. The conditional formatting was
working
> > > fine,
> > > > but I can only have 3 conditional formats. I will be entering many
> > > different
> > > > jobs numbers and will want all of them to have a conditional format
so
> > > that
> > > > if I enter the same job number somewhere else within the same
worksheet,
> > > it
> > > > will be highlighted or the font will change to bold. Is it possible
to
> > > > conditional format so many different numbers?
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > Conditional formatting will do it with a formula of say
> > > > >
> > > > > =COUNTIF(A:IV,A1)>1
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "css" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > If I enter a number into any cell which has already been entered
> > into
> > > > > another
> > > > > > cell, regardless of row or column, is there any way in which my
> > > attention
> > > > > > could be drawn to the fact that this number is already in the
> > > worksheet,
> > > > > i.e.
> > > > > > by highlighting or something similar.
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
> >

GOT IT! I wasn't using the first formula you suggested correctly. But I got
there in the end (changed "cell value is" to "formula is") and it's working
just how I need it to. I'm still a novice but very slowly getting there.
Many, many thanks.

"Bob Phillips" wrote:

> Are you saying that if anything gets duplicated then you want it to be
> coloured, with a different colour for every different value duplicated?
> There are only 56 colours in the colour palette, and many of them are very
> similar, you would never spot the difference.
>
> --
> HTH
>
> Bob Phillips
>
> "css" <[email protected]> wrote in message
> news:[email protected]...
> > This looks great. I will add that as it is something I will deffinatley
> use.
> > However, for this purpose, 30 conditional formats still isn't enough (I
> know,
> > I'm greedy!). I need to keep a record of all job numbers for a year so
> there
> > will be hundreds of jobs numbers. Rather than setting a conditional format
> > for a specific number, is there any way of creating a general rule that
> > should 'any' number be entered more than once in the same spreadsheet, it
> > will alert me in some way either by a message flashing up or simply change
> > the font colour etc.
> >
> > "Bob Phillips" wrote:
> >
> > > Don't know what happened here. It should read ...
> > >
> > > Are you up for an add-in?
> > >
> > > http://www.xldynamic.com/source/xld.....Download.html
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > Are you up for an add-in?
> > > >
> > > > Public Function MultiCat(ByRef rRng As Range, _
> > > > Optional ByVal sDelim As String = "") As String
> > > > Dim rCell As Range
> > > > For Each rCell In rRng
> > > > If rCell.Value <> "" Then
> > > > MultiCat = MultiCat & sDelim & rCell.Text
> > > > End If
> > > > Next rCell
> > > > MultiCat = Mid(MultiCat, Len(sDelim) + 1)
> > > > End Function
> > > >
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "css" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I have run into a small problem. The conditional formatting was
> working
> > > > fine,
> > > > > but I can only have 3 conditional formats. I will be entering many
> > > > different
> > > > > jobs numbers and will want all of them to have a conditional format
> so
> > > > that
> > > > > if I enter the same job number somewhere else within the same
> worksheet,
> > > > it
> > > > > will be highlighted or the font will change to bold. Is it possible
> to
> > > > > conditional format so many different numbers?
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > Conditional formatting will do it with a formula of say
> > > > > >
> > > > > > =COUNTIF(A:IV,A1)>1
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > "css" <[email protected]> wrote in message
> > > > > > news:[email protected]...
> > > > > > > If I enter a number into any cell which has already been entered
> > > into
> > > > > > another
> > > > > > > cell, regardless of row or column, is there any way in which my
> > > > attention
> > > > > > > could be drawn to the fact that this number is already in the
> > > > worksheet,
> > > > > > i.e.
> > > > > > > by highlighting or something similar.
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>