Posted to microsoft.public.excel.worksheet.functions
|
|
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.
|