Prev: Maintaining variables after program ends
Next: Passing Parameters to a Pivot Table
From: JGPatrick on 11 Mar 2010 18:01
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.


From: Dennis Tucker on 11 Mar 2010 23:07
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" <JGPatrick(a)discussions.microsoft.com> wrote in message
news:7F41F3EE-54EB-4C77-979D-2A4A384B6D19(a)microsoft.com...
> 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.
>
>
From: JGPatrick on 12 Mar 2010 17:21
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" <JGPatrick(a)discussions.microsoft.com> wrote in message
> news:7F41F3EE-54EB-4C77-979D-2A4A384B6D19(a)microsoft.com...
> > 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.
> >
> >
> .
>
 | 
Pages: 1
Prev: Maintaining variables after program ends
Next: Passing Parameters to a Pivot Table