Wish Mr. Johnc could provide step by step as an example of what to do. Sorry,
but I'm just a beginner on excel. Thanks

Hi,

Even after looking over the 11/5 message I am not sure what you are trying
to do, but I am guessing you have a large range of cells containing 3 digit
numbers in each cell. And you want to enter a 1 to 3 digit number in a cell,
say A1 and have the all cells in the range format with a given color.

Suppose the 3 digit numbers are located in B1:J100, highlight them and
choose the command
Format, Conditional Formatting, and from the second drop down pick equal to
and in the 3rd box enter =$A$1 Click the Format button and choose a color on
the Patterns tab, then click OK twice.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"ghinzrey" wrote:

Wish Mr. Johnc could provide step by step as an example of what to do. Sorry,
but I'm just a beginner on excel. Thanks

Thanks for your advise. Yes, you're right I've got lot of cell datas now. All
I need is the command structure on conditional formatting where at least I
could make four color shadings on each look up cell values. Say, need to
look up cells containing 123 (in any order) and color green, next, 527,
yellow and so on. Could this be possible?

"Shane Devenshire" wrote:

Hi,

Even after looking over the 11/5 message I am not sure what you are trying
to do, but I am guessing you have a large range of cells containing 3 digit
numbers in each cell. And you want to enter a 1 to 3 digit number in a cell,
say A1 and have the all cells in the range format with a given color.

Suppose the 3 digit numbers are located in B1:J100, highlight them and
choose the command
Format, Conditional Formatting, and from the second drop down pick equal to
and in the 3rd box enter =$A$1 Click the Format button and choose a color on
the Patterns tab, then click OK twice.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"ghinzrey" wrote:

Wish Mr. Johnc could provide step by step as an example of what to do. Sorry,
but I'm just a beginner on excel. Thanks

My response was for one set of 3 numbers, not multiple sets (your question
did not indicate such). Unless you have xl2007, you aren't going to be able
do do much beyond 3 color formats anyway, without using VBA. And that's a
whole different ball of wax.

Perhaps if you explained what you were trying to accomplish... I.E: I want
to highlight cells that have the 3 digits of 245 green, 335 yellow, etc....,
but then to do what with them? What is your ultimate outcome. Are you
correcting them....or?
--
** John C **


"ghinzrey" wrote:

Wish Mr. Johnc could provide step by step as an example of what to do. Sorry,
but I'm just a beginner on excel. Thanks

Sorry to disturb you Mr. John C.

My 3 numbers cell datas are game results on daily basis. I'm trying to study
these results to evaluate any pattern of manipulation. Well, its fine with me
if you could provide 3 color formats.

"John C" wrote:

My response was for one set of 3 numbers, not multiple sets (your question
did not indicate such). Unless you have xl2007, you aren't going to be able
do do much beyond 3 color formats anyway, without using VBA. And that's a
whole different ball of wax.

Perhaps if you explained what you were trying to accomplish... I.E: I want
to highlight cells that have the 3 digits of 245 green, 335 yellow, etc....,
but then to do what with them? What is your ultimate outcome. Are you
correcting them....or?
--
** John C **


"ghinzrey" wrote:

Wish Mr. Johnc could provide step by step as an example of what to do. Sorry,
but I'm just a beginner on excel. Thanks

Okay, now that we know the premise of the numbers, my first piece of advice
is to enter all your data as text, and not as numbers formatted as text.
In my sample data, I set up cells B1:J1000 as random 3 digit numbers
(entered as text). Since I can use conditional formatting for up to 3
conditions, I wanted those 3 conditions to match up in cells A1, A2, and A3
(those are the cells I type in that will determine what gets highlighted.
I then selected the entire range from B1:J1000 (with B1 as my active cell),
and did the following as conditional formats.
Condition 1:
=OR(B1=$A$1,B1=LEFT($A$1,1)&RIGHT($A$1,1)&MID($A$1 ,2,1),B1=RIGHT($A$1,1)&LEFT($A$1,2),B1=RIGHT($A$1, 1)&MID($A$1,2,1)&LEFT($A$1,1),B1=MID($A$1,2,1)&LEF T($A$1,1)&RIGHT($A$1,1),B1=MID($A$1,2,1)&RIGHT($A$ 1,1)&LEFT($A$1,1))
Condition 2:
=OR(B1=$A$2,B1=LEFT($A$2,1)&RIGHT($A$2,1)&MID($A$2 ,2,1),B1=RIGHT($A$2,1)&LEFT($A$2,2),B1=RIGHT($A$2, 1)&MID($A$2,2,1)&LEFT($A$2,1),B1=MID($A$2,2,1)&LEF T($A$2,1)&RIGHT($A$2,1),B1=MID($A$2,2,1)&RIGHT($A$ 2,1)&LEFT($A$2,1))
Condition 3:
=OR(B1=$A$3,B1=LEFT($A$3,1)&RIGHT($A$3,1)&MID($A$3 ,2,1),B1=RIGHT($A$3,1)&LEFT($A$3,2),B1=RIGHT($A$3, 1)&MID($A$3,2,1)&LEFT($A$3,1),B1=MID($A$3,2,1)&LEF T($A$3,1)&RIGHT($A$3,1),B1=MID($A$3,2,1)&RIGHT($A$ 3,1)&LEFT($A$3,1))

Note: There is an add-in that can supposedly handle up to 30 conditional
formats located at:
http://www.xldynamic.com/source/xld.....Download.html
But I find it buggy, and has a tendency to re-format the cells with numbers
back to general (and thus removing preceding 0s). You may wanna see if you
can get it to work better than I could.

In addition, there is a snippet of VBA code that may give you some ideas on
how to work better with VBA and conditional formatting. That link is listed
below. I did not try that VBA code at all (didn't really look at it), but you
may want to see if you could do anything with it. Anyway, I hope this helps
at least some.
http://www.microsoft.com/office/comm...5-7fc51d289191

--
** John C **

"ghinzrey" wrote:

Sorry to disturb you Mr. John C.

My 3 numbers cell datas are game results on daily basis. I'm trying to study
these results to evaluate any pattern of manipulation. Well, its fine with me
if you could provide 3 color formats.

"John C" wrote:

My response was for one set of 3 numbers, not multiple sets (your question
did not indicate such). Unless you have xl2007, you aren't going to be able
do do much beyond 3 color formats anyway, without using VBA. And that's a
whole different ball of wax.

Perhaps if you explained what you were trying to accomplish... I.E: I want
to highlight cells that have the 3 digits of 245 green, 335 yellow, etc....,
but then to do what with them? What is your ultimate outcome. Are you
correcting them....or?
--
** John C **


"ghinzrey" wrote:

Wish Mr. Johnc could provide step by step as an example of what to do. Sorry,
but I'm just a beginner on excel. Thanks

You call me out here, I respond, and then nothing????
--
** John C **


"ghinzrey" wrote:

Sorry to disturb you Mr. John C.

My 3 numbers cell datas are game results on daily basis. I'm trying to study
these results to evaluate any pattern of manipulation. Well, its fine with me
if you could provide 3 color formats.

"John C" wrote:

My response was for one set of 3 numbers, not multiple sets (your question
did not indicate such). Unless you have xl2007, you aren't going to be able
do do much beyond 3 color formats anyway, without using VBA. And that's a
whole different ball of wax.

Perhaps if you explained what you were trying to accomplish... I.E: I want
to highlight cells that have the 3 digits of 245 green, 335 yellow, etc....,
but then to do what with them? What is your ultimate outcome. Are you
correcting them....or?
--
** John C **


"ghinzrey" wrote:

Wish Mr. Johnc could provide step by step as an example of what to do. Sorry,
but I'm just a beginner on excel. Thanks