Have created a three digit (including zeros) numerical database in the range
A2-A35 expanding now up to CC2-CC35. Note that leading zeros are not shown.
My wish is to find the cells of same numerics in any random or repetitive
(ex:567,576,657,675,756,765) in the whole database and to color shade. How
can I?

Use a macro ... There are 120 different three digit combinations that can be made from 10 digits,
without repeats, so you cannot have a unique color for each combination of digits. Do you only want
to highlight one at a time?

HTH,
Bernie
MS Excel MVP


"ghinzrey" wrote in message
...
Have created a three digit (including zeros) numerical database in the range
A2-A35 expanding now up to CC2-CC35. Note that leading zeros are not shown.
My wish is to find the cells of same numerics in any random or repetitive
(ex:567,576,657,675,756,765) in the whole database and to color shade. How
can I?

Hi,

I'm going to set up a fancy solution, it may be overkill:

1. I set up a range of three cells where you can enter you 3 digits, one in
each cell. I used P1:P3 and for a test I entered 5, 6, and 7 respectively.
2. Set up a series of cell containing the following formulas:

=--(P$1&P$2&P$3)
=--(P$1&P$3&P$2)
=--(P$2&P$1&P$3)
=--(P$2&P$3&P$1)
=--(P$3&P$2&P$1)
=--(P$3&P$1&P$2)

I put these in cell P5:P10. These are all the 3 digit combinations of the
entries in P1:P3.

3. Highlight your range and choose Format, Conditional Formatting, and
choose Formula is from the first drop down pick Formula is
4. In the second box enter the following formula:

=OR(Q1=$P$5,Q1=$P$6,Q1=$P$7,Q1=$P$8,Q1=$P$9,Q1=$P$ 10)

5. Click Format, and pick a color on the Patterns tab.

The beauty of this is you can enter any three number in P1:P3 and you will
be applying the conditional formatting to three digit combinations of those
numbers.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"ghinzrey" wrote:

Have created a three digit (including zeros) numerical database in the range
A2-A35 expanding now up to CC2-CC35. Note that leading zeros are not shown.
My wish is to find the cells of same numerics in any random or repetitive
(ex:567,576,657,675,756,765) in the whole database and to color shade. How
can I?

Its more than 120 as these include triple an double digit (ex:111; 122). Note
that each cell has three digit and are randomly repetitive. These is a three
digit numbers game results and updated daily. What I wish is say, I want to
highlight the cells that contains ex: 087 in any order. Can all datas be
highlighted by a single command?

"Bernie Deitrick" wrote:

Use a macro ... There are 120 different three digit combinations that can be made from 10 digits,
without repeats, so you cannot have a unique color for each combination of digits. Do you only want
to highlight one at a time?

HTH,
Bernie
MS Excel MVP


"ghinzrey" wrote in message
...
Have created a three digit (including zeros) numerical database in the range
A2-A35 expanding now up to CC2-CC35. Note that leading zeros are not shown.
My wish is to find the cells of same numerics in any random or repetitive
(ex:567,576,657,675,756,765) in the whole database and to color shade. How
can I?

Sorry, there's three digits in each cell except that leading zeros aren't
seen. Got a lot of datas now and means repetitive. Would like reviewing datas
by color shading. Say, I need looking up cells containing 123 in any order
and thus color shading. Can this be possible?

"ShaneDevenshire" wrote:

Hi,

I'm going to set up a fancy solution, it may be overkill:

1. I set up a range of three cells where you can enter you 3 digits, one in
each cell. I used P1:P3 and for a test I entered 5, 6, and 7 respectively.
2. Set up a series of cell containing the following formulas:

=--(P$1&P$2&P$3)
=--(P$1&P$3&P$2)
=--(P$2&P$1&P$3)
=--(P$2&P$3&P$1)
=--(P$3&P$2&P$1)
=--(P$3&P$1&P$2)

I put these in cell P5:P10. These are all the 3 digit combinations of the
entries in P1:P3.

3. Highlight your range and choose Format, Conditional Formatting, and
choose Formula is from the first drop down pick Formula is
4. In the second box enter the following formula:

=OR(Q1=$P$5,Q1=$P$6,Q1=$P$7,Q1=$P$8,Q1=$P$9,Q1=$P$ 10)

5. Click Format, and pick a color on the Patterns tab.

The beauty of this is you can enter any three number in P1:P3 and you will
be applying the conditional formatting to three digit combinations of those
numbers.

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire


"ghinzrey" wrote:

Have created a three digit (including zeros) numerical database in the range
A2-A35 expanding now up to CC2-CC35. Note that leading zeros are not shown.
My wish is to find the cells of same numerics in any random or repetitive
(ex:567,576,657,675,756,765) in the whole database and to color shade. How
can I?