Prefil Color Pattern in Excel
 

Hello,

I am looking to see if it is possible to automatically change the colors of
cells in accordance with a number....

ie. I have a list of numbers, lets say Ten rows long, 3 numbers per row,
preset from 1 - 5

Row.1 1, 2, 3
Row.2 1, 2, 5
Row.3 3, 4, 5
Row.4 2, 4, 5

etc.

I would like to take that list and have it prefill another worksheet, with a
black color pattern, that have the numbers in the cell
Col.1 Col.2 Col.3 Col.4......
Row.1 1 1 1 1
Row.2 2 2 2 2
Row.3 3 3 3 3
Row.4 4 4 4 4
Row.5 5 5 5 5

I am unsure if it would be a Macro or some type of automatic formula.

Thank you.

Prefil Color Pattern in Excel
 

Hazarding a deep guess as to what you're after ...

Here's a sample construct:
http://www.savefile.com/files/6771585
CondFormat_TransposeMatch_Nos_in_OtherSheet.xls

In sheet: X,

in A1:C4 is the reference data:

1 2 3
1 2 5
3 4 5
2 4 5

Put in D1: List1
Copy D1 down to D4

(this quick fills D1:D4 with: List1, List2, List3, List4)

Select A1:D4
Click Insert Name Create Check "Right column" OK

(This quickly creates defined names for each of the 4 rows: A1:C1, A2:C2 ...
A4:C4 using the text in the right col of the selection: List1, List2, ..
List4)

Then in sheet: Y,

in A1:D5 are the numbers :

1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5

Select A1:D5 (with A1 active)
Click Format Conditional Formatting
Under Condition 1, make the settings:
Formula is:
=ISNUMBER(MATCH(A1,INDIRECT("List"&COLUMN(A1)),0))
Click Format button Patterns tab Brown? OK
Click OK at the main dialog

Sheet Y will be conditionally formatted "columnwise" i.e. for A1:A5, the
cells where the numbers 1 - 5 match any of the 3 numbers in X's A1:C1
("List1") will be filled brown. For B1:B5, cells will be filled brown where
the numbers match those in "List2", and so on.

(a sort of "Transpose-Match" conditional formatting <g)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David" wrote in message
...
Hello,

I am looking to see if it is possible to automatically change the colors
of
cells in accordance with a number....

ie. I have a list of numbers, lets say Ten rows long, 3 numbers per row,
preset from 1 - 5

Row.1 1, 2, 3
Row.2 1, 2, 5
Row.3 3, 4, 5
Row.4 2, 4, 5

etc.

I would like to take that list and have it prefill another worksheet, with
a
black color pattern, that have the numbers in the cell
Col.1 Col.2 Col.3 Col.4......
Row.1 1 1 1 1
Row.2 2 2 2 2
Row.3 3 3 3 3
Row.4 4 4 4 4
Row.5 5 5 5 5

I am unsure if it would be a Macro or some type of automatic formula.

Thank you.

Prefil Color Pattern in Excel
 

That is exactly what I needed... Thank you

David

"Max" wrote:

Hazarding a deep guess as to what you're after ...

Here's a sample construct:
http://www.savefile.com/files/6771585
CondFormat_TransposeMatch_Nos_in_OtherSheet.xls

In sheet: X,

in A1:C4 is the reference data:

1 2 3
1 2 5
3 4 5
2 4 5

Put in D1: List1
Copy D1 down to D4

(this quick fills D1:D4 with: List1, List2, List3, List4)

Select A1:D4
Click Insert Name Create Check "Right column" OK

(This quickly creates defined names for each of the 4 rows: A1:C1, A2:C2 ...
A4:C4 using the text in the right col of the selection: List1, List2, ..
List4)

Then in sheet: Y,

in A1:D5 are the numbers :

1 1 1 1
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5

Select A1:D5 (with A1 active)
Click Format Conditional Formatting
Under Condition 1, make the settings:
Formula is:
=ISNUMBER(MATCH(A1,INDIRECT("List"&COLUMN(A1)),0))
Click Format button Patterns tab Brown? OK
Click OK at the main dialog

Sheet Y will be conditionally formatted "columnwise" i.e. for A1:A5, the
cells where the numbers 1 - 5 match any of the 3 numbers in X's A1:C1
("List1") will be filled brown. For B1:B5, cells will be filled brown where
the numbers match those in "List2", and so on.

(a sort of "Transpose-Match" conditional formatting <g)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David" wrote in message
...
Hello,

I am looking to see if it is possible to automatically change the colors
of
cells in accordance with a number....

ie. I have a list of numbers, lets say Ten rows long, 3 numbers per row,
preset from 1 - 5

Row.1 1, 2, 3
Row.2 1, 2, 5
Row.3 3, 4, 5
Row.4 2, 4, 5

etc.

I would like to take that list and have it prefill another worksheet, with
a
black color pattern, that have the numbers in the cell
Col.1 Col.2 Col.3 Col.4......
Row.1 1 1 1 1
Row.2 2 2 2 2
Row.3 3 3 3 3
Row.4 4 4 4 4
Row.5 5 5 5 5

I am unsure if it would be a Macro or some type of automatic formula.

Thank you.

Prefil Color Pattern in Excel
 

Great to hear that, David !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"David" wrote in message
...
That is exactly what I needed... Thank you

David