Is there any way in VBA to apply a colorscale, icon set, or databar to a
range, but have the color scale, icons, and databars show up in a different
range?

For instance, suppose in B1:B3 you have the number of sales of Robert, John,
and Sue, and in C1:C3 you have the dollar value of those sales, and that in
D1:D3 you want to represent the number of sales with a color scale and the
dollar value of sales with an icon set. In other words, in D1:D3 you want a
visual representation of the dollar value of sales overlayed on a visual
representation of the number of sales.

This is a toy example, I have a more complex application in mind.

It is fairly easy to represent numbers with a color. If you wanted to
highlight the dollars by changing their cell's fill color(background), then
this can be done in several ways.

1. Conditional formatting: this is the easy and simplest way but it is also
the most limited way. This does not require macros. All of the other
methods require macros.

2. Macros 1: Setup dollar range bins and each bin is associated with a
color.

3. Macros 2: True Color(24 bits) can represent over 16 million colors. The
value zero(0) represents BLACK. The maximum value 16777215 represents
WHITE. All of the colors come from values between. The range 0-16777215
can be associated with a range of dollars.

ICON SET??

Dennis

"JGPatrick" wrote in message
...
Is there any way in VBA to apply a colorscale, icon set, or databar to a
range, but have the color scale, icons, and databars show up in a
different
range?

For instance, suppose in B1:B3 you have the number of sales of Robert,
John,
and Sue, and in C1:C3 you have the dollar value of those sales, and that
in
D1:D3 you want to represent the number of sales with a color scale and the
dollar value of sales with an icon set. In other words, in D1:D3 you want
a
visual representation of the dollar value of sales overlayed on a visual
representation of the number of sales.

This is a toy example, I have a more complex application in mind.

Thanks for the suggestions, especially the 2nd macro suggestion.

"Dennis Tucker" wrote:

It is fairly easy to represent numbers with a color. If you wanted to
highlight the dollars by changing their cell's fill color(background), then
this can be done in several ways.

1. Conditional formatting: this is the easy and simplest way but it is also
the most limited way. This does not require macros. All of the other
methods require macros.

2. Macros 1: Setup dollar range bins and each bin is associated with a
color.

3. Macros 2: True Color(24 bits) can represent over 16 million colors. The
value zero(0) represents BLACK. The maximum value 16777215 represents
WHITE. All of the colors come from values between. The range 0-16777215
can be associated with a range of dollars.

ICON SET??

Dennis

"JGPatrick" wrote in message
...
Is there any way in VBA to apply a colorscale, icon set, or databar to a
range, but have the color scale, icons, and databars show up in a
different
range?

For instance, suppose in B1:B3 you have the number of sales of Robert,
John,
and Sue, and in C1:C3 you have the dollar value of those sales, and that
in
D1:D3 you want to represent the number of sales with a color scale and the
dollar value of sales with an icon set. In other words, in D1:D3 you want
a
visual representation of the dollar value of sales overlayed on a visual
representation of the number of sales.

This is a toy example, I have a more complex application in mind.


.