Conditional Formating on three digit numeric cell database
 

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?

Conditional Formating on three digit numeric cell database
 

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?

Conditional Formating on three digit numeric cell database
 

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?

Conditional Formating on three digit numeric cell database
 

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?

Conditional Formating on three digit numeric cell database
 

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?