In the Format Cell tools, there is a pallet of 56 colors and 18 patterns that
could be applied (manually).

I need to display "remote" data graphically alongside a row of affliated
information in a worksheet. My "remote data" may be a number or a text
string. What I was hoping to do is create a function which would work
similar to a Vlookup. I would like the resulting cell (with the function) to
contain either color fill and or patterns. No other information needs to be
displayed in the cell except for the color and/or pattern.

Any thoughts on how this could be automated?

You won't be able to do this using a formula, as a function cannot be
used to change the format of a cell. You will need to use a macro.

Hope this helps.

Pete

On Nov 26, 5:24*pm, BrianG wrote:
In the Format Cell tools, there is a pallet of 56 colors and 18 patterns that
could be applied (manually).

I need to display "remote" data graphically alongside a row of affliated
information in a worksheet. * My "remote data" may be a number or a text
string. *What I was hoping to do is create a function which would work
similar to a Vlookup. *I would like the resulting cell (with the function) to
contain either color fill and or patterns. *No other information needs to be
displayed in the cell except for the color and/or pattern.

Any thoughts on how this could be automated?

Hi,

You problem is not that it can't be done, its that there are only 3
conditions which can be applied at one time in 2003 (in 2007 you can go to
town).

You can do this with a formula as follows:
1. Put a formula into the cells that evalute to a number/text which will
represent the formatting you wish.
2. Apply a conditional format to the cell to color it (see below)
3. Applye Format, Cells, Number, Custom and enter ;;; on the type line.

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose equal to
4. In the second box enter the number
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.
to add a 2nd and 3rd format click the Add button before clicking the final OK.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Format only cells that contain
4. Choose equal to and enter your number in the next box
5. Click the Format button and choose a format.
6. Click OK twice

Repeat the above steps for each format you want to apply, no limit in 2007
If this helps, please click the Yes button.

In 2003 to go beyond 3 colors/formats you will need to use VBA

Cheers,
Shane Devenshire

"BrianG" wrote:

In the Format Cell tools, there is a pallet of 56 colors and 18 patterns that
could be applied (manually).

I need to display "remote" data graphically alongside a row of affliated
information in a worksheet. My "remote data" may be a number or a text
string. What I was hoping to do is create a function which would work
similar to a Vlookup. I would like the resulting cell (with the function) to
contain either color fill and or patterns. No other information needs to be
displayed in the cell except for the color and/or pattern.

Any thoughts on how this could be automated?

"Pete_UK" wrote:

You won't be able to do this using a formula, as a function cannot be
used to change the format of a cell. You will need to use a macro.

Hope this helps.

Pete


It answers a sneaking suspicion. I had not thought of a macro route. Any
idea on how a macro could make the selection?

Thanks Shane,

I already use Conditional formatting extensively but the limitation of 4
formats (3 conditions plus a null) is what I am running up against. I am
trying to put graphics to geological data for quick views and I need more
robust formatting options.

I was hoping that a function call (or perhaps VBA) could auto select those
colors and patterns based on a known lookup table (Value, Color, Pattern) and
a pointer to a value reference.

I could certainly run a macro but I cannot think of how the macro would
interpret differing values and pick the right colors/patterns. My abilities
are limited to non existant in the coding department.

Brian G.

"Shane Devenshire" wrote:

Hi,

You problem is not that it can't be done, its that there are only 3
conditions which can be applied at one time in 2003 (in 2007 you can go to
town).

You can do this with a formula as follows:
1. Put a formula into the cells that evalute to a number/text which will
represent the formatting you wish.
2. Apply a conditional format to the cell to color it (see below)
3. Applye Format, Cells, Number, Custom and enter ;;; on the type line.

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose equal to
4. In the second box enter the number
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.
to add a 2nd and 3rd format click the Add button before clicking the final OK.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Format only cells that contain
4. Choose equal to and enter your number in the next box
5. Click the Format button and choose a format.
6. Click OK twice

Repeat the above steps for each format you want to apply, no limit in 2007
If this helps, please click the Yes button.

In 2003 to go beyond 3 colors/formats you will need to use VBA

Cheers,
Shane Devenshire

"BrianG" wrote:

In the Format Cell tools, there is a pallet of 56 colors and 18 patterns that
could be applied (manually).

I need to display "remote" data graphically alongside a row of affliated
information in a worksheet. My "remote data" may be a number or a text
string. What I was hoping to do is create a function which would work
similar to a Vlookup. I would like the resulting cell (with the function) to
contain either color fill and or patterns. No other information needs to be
displayed in the cell except for the color and/or pattern.

Any thoughts on how this could be automated?

Hi,

here is an example, you may want to be fancier with fill patterns. This
formats the cells based and 100 point increments.

Sub ColorCoding()
Dim cell As Range
For Each cell In Selection
With cell.Interior
Select Case cell
Case 0 To 99
.ColorIndex = 38
Case 100 To 199
.ColorIndex = 44
Case 200 To 299
.ColorIndex = 36
Case 300 To 399
.ColorIndex = 35
Case 400 To 499
.ColorIndex = 34
Case 500 To 599
.ColorIndex = 37
Case 600 To 699
.ColorIndex = 39
Case 700 To 799
.ColorIndex = 7
Case 800 To 899
.ColorIndex = 4
Case Else
.ColorIndex = 47
End Select
End With
Next cell
End Sub

You can turn on the recorder and get the code for the pattern and go from
there

Hope this helps, if so please click the Yes button

Cheers,
Shane Devenshire


"BrianG" wrote:

Thanks Shane,

I already use Conditional formatting extensively but the limitation of 4
formats (3 conditions plus a null) is what I am running up against. I am
trying to put graphics to geological data for quick views and I need more
robust formatting options.

I was hoping that a function call (or perhaps VBA) could auto select those
colors and patterns based on a known lookup table (Value, Color, Pattern) and
a pointer to a value reference.

I could certainly run a macro but I cannot think of how the macro would
interpret differing values and pick the right colors/patterns. My abilities
are limited to non existant in the coding department.

Brian G.

"Shane Devenshire" wrote:

Hi,

You problem is not that it can't be done, its that there are only 3
conditions which can be applied at one time in 2003 (in 2007 you can go to
town).

You can do this with a formula as follows:
1. Put a formula into the cells that evalute to a number/text which will
represent the formatting you wish.
2. Apply a conditional format to the cell to color it (see below)
3. Applye Format, Cells, Number, Custom and enter ;;; on the type line.

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose equal to
4. In the second box enter the number
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.
to add a 2nd and 3rd format click the Add button before clicking the final OK.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Format only cells that contain
4. Choose equal to and enter your number in the next box
5. Click the Format button and choose a format.
6. Click OK twice

Repeat the above steps for each format you want to apply, no limit in 2007
If this helps, please click the Yes button.

In 2003 to go beyond 3 colors/formats you will need to use VBA

Cheers,
Shane Devenshire

"BrianG" wrote:

In the Format Cell tools, there is a pallet of 56 colors and 18 patterns that
could be applied (manually).

I need to display "remote" data graphically alongside a row of affliated
information in a worksheet. My "remote data" may be a number or a text
string. What I was hoping to do is create a function which would work
similar to a Vlookup. I would like the resulting cell (with the function) to
contain either color fill and or patterns. No other information needs to be
displayed in the cell except for the color and/or pattern.

Any thoughts on how this could be automated?

Thanks Shane,

I will have to play with this a bit and see how to get it to work for my
case. I certainly "understand" the code above and can now extract the same
for the patterning. Hardcoding the selections works for me too since I will
be setting the reference values in advance and once set, there is no real
reason to alter the options.

I will try and post back after I had some time to digest this more.

BrianG.

"Shane Devenshire" wrote:

Hi,

here is an example, you may want to be fancier with fill patterns. This
formats the cells based and 100 point increments.

Sub ColorCoding()
Dim cell As Range
For Each cell In Selection
With cell.Interior
Select Case cell
Case 0 To 99
.ColorIndex = 38
Case 100 To 199
.ColorIndex = 44
Case 200 To 299
.ColorIndex = 36
Case 300 To 399
.ColorIndex = 35
Case 400 To 499
.ColorIndex = 34
Case 500 To 599
.ColorIndex = 37
Case 600 To 699
.ColorIndex = 39
Case 700 To 799
.ColorIndex = 7
Case 800 To 899
.ColorIndex = 4
Case Else
.ColorIndex = 47
End Select
End With
Next cell
End Sub

You can turn on the recorder and get the code for the pattern and go from
there

Hope this helps, if so please click the Yes button

Cheers,
Shane Devenshire


"BrianG" wrote:

Thanks Shane,

I already use Conditional formatting extensively but the limitation of 4
formats (3 conditions plus a null) is what I am running up against. I am
trying to put graphics to geological data for quick views and I need more
robust formatting options.

I was hoping that a function call (or perhaps VBA) could auto select those
colors and patterns based on a known lookup table (Value, Color, Pattern) and
a pointer to a value reference.

I could certainly run a macro but I cannot think of how the macro would
interpret differing values and pick the right colors/patterns. My abilities
are limited to non existant in the coding department.

Brian G.

"Shane Devenshire" wrote:

Hi,

You problem is not that it can't be done, its that there are only 3
conditions which can be applied at one time in 2003 (in 2007 you can go to
town).

You can do this with a formula as follows:
1. Put a formula into the cells that evalute to a number/text which will
represent the formatting you wish.
2. Apply a conditional format to the cell to color it (see below)
3. Applye Format, Cells, Number, Custom and enter ;;; on the type line.

To conditionally format your cell(s):

In 2003:
1. Select the cell you want to format
2. Choose Format, Conditional Formatting
3. Choose equal to
4. In the second box enter the number
5. Click the Format button
6. Choose a color on the Patterns tab (or any available option)
7. Click OK twice.
to add a 2nd and 3rd format click the Add button before clicking the final OK.

In 2007:
1. Highlight all the cells on the rows you want formatted
2. Choose Home, Conditional Formatting, New Rule
3. Choose Format only cells that contain
4. Choose equal to and enter your number in the next box
5. Click the Format button and choose a format.
6. Click OK twice

Repeat the above steps for each format you want to apply, no limit in 2007
If this helps, please click the Yes button.

In 2003 to go beyond 3 colors/formats you will need to use VBA

Cheers,
Shane Devenshire

"BrianG" wrote:

In the Format Cell tools, there is a pallet of 56 colors and 18 patterns that
could be applied (manually).

I need to display "remote" data graphically alongside a row of affliated
information in a worksheet. My "remote data" may be a number or a text
string. What I was hoping to do is create a function which would work
similar to a Vlookup. I would like the resulting cell (with the function) to
contain either color fill and or patterns. No other information needs to be
displayed in the cell except for the color and/or pattern.

Any thoughts on how this could be automated?