I know I can read the RGB value of a color in VBA.

Is there a consistent way to do a calculation on those values to visually
return the same color but lighter (dimmer)?

Thanks,
Jeremy

Excel only displays 56 colors at a time. The color value is RGB, but it
won't be the RGB value you set unless are just lucky. It sets the value to
the closest color to the RGB value.

If you go into Tools=Options=Color Tab, you will see there are very few
colors I would call dimmer shades of other colors in the palette. You can
create your own custom palette of course.

See David McRitchie's page on this:
http://www.mvps.org/dmcritchie/excel/colors.htm

--
Regards,
Tom Ogilvy


"Jeremy Gollehon" wrote in message
...
I know I can read the RGB value of a color in VBA.

Is there a consistent way to do a calculation on those values to visually
return the same color but lighter (dimmer)?

Thanks,
Jeremy

Hi Tom,
Thanks for the reply.
Let me reword this a little (It should really have a different title).

Lets say I want to change the color of [Color 47] (RGB 102,102,153) in the
color pallete.
Currently I can go to Tools Options Color Modify Custom, and slide
the slider up a little to get a lighter color.

Is there a way to do this programatically?
The real problem is that depending on where you start in the color range,
the percentage change in Red, Green, or Blue is different as you move the
slider. I was hoping someone out there might have tackled something like
this before.

Thanks again,
Jeremy


Tom Ogilvy wrote:
Excel only displays 56 colors at a time. The color value is RGB, but
it won't be the RGB value you set unless are just lucky. It sets the
value to the closest color to the RGB value.

If you go into Tools=Options=Color Tab, you will see there are very
few colors I would call dimmer shades of other colors in the palette.
You can create your own custom palette of course.

See David McRitchie's page on this:
http://www.mvps.org/dmcritchie/excel/colors.htm


"Jeremy Gollehon" wrote in message
...
I know I can read the RGB value of a color in VBA.

Is there a consistent way to do a calculation on those values to
visually return the same color but lighter (dimmer)?

Thanks,
Jeremy

Hi Jeremy

I know I can read the RGB value of a color in VBA.

If you use customized palette colours be sure to read the RGB of the color
index and not the RGB of some format. Otherwise you may return the default
palette colour rather than the actual colour (depends on other factors
related in your overall code).

Is there a consistent way to do a calculation on those values to visually
return the same color but lighter (dimmer)?

Depending on what you mean by making lighter/darker it probably involves
converting RGB to HSL, adjusting the L value +/- then reconverting the new
HSL to RGB. Then perhaps customizing one of the palette colours with the new
RGB.

But if all you want to do is format a similar colour lighter/darker a crude
way is mixing pattern "grey" shades with pattern colour white through grey
to black. If you want to go further over 2000 unique colours can be
"simulated" using pattern shades with the default palette of 46 unique
colours (56-10 duplicates).

Regards,
Peter T

"Jeremy Gollehon" wrote in message
...
I know I can read the RGB value of a color in VBA.

Is there a consistent way to do a calculation on those values to visually
return the same color but lighter (dimmer)?

Thanks,
Jeremy

Jeremy,

"Is there a way to do this programmatically?"
I think you are looking for...
ActiveWorkbook.Colors(47) = RGB(102, 125, 153)

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Jeremy Gollehon"
wrote in message
...
Hi Tom,
Thanks for the reply.
Let me reword this a little (It should really have a different title).

Lets say I want to change the color of [Color 47] (RGB 102,102,153) in the
color pallete.
Currently I can go to Tools Options Color Modify Custom, and slide
the slider up a little to get a lighter color.

Is there a way to do this programatically?
The real problem is that depending on where you start in the color range,
the percentage change in Red, Green, or Blue is different as you move the
slider. I was hoping someone out there might have tackled something like
this before.
Thanks again,
Jeremy


Tom Ogilvy wrote:
Excel only displays 56 colors at a time. The color value is RGB, but
it won't be the RGB value you set unless are just lucky. It sets the
value to the closest color to the RGB value.

If you go into Tools=Options=Color Tab, you will see there are very
few colors I would call dimmer shades of other colors in the palette.
You can create your own custom palette of course.

See David McRitchie's page on this:
http://www.mvps.org/dmcritchie/excel/colors.htm


"Jeremy Gollehon" wrote in message
...
I know I can read the RGB value of a color in VBA.

Is there a consistent way to do a calculation on those values to
visually return the same color but lighter (dimmer)?

Thanks,
Jeremy

Yes! This is more to what I'm getting at Peter.
I'll look into converting RGB to HSL, then adjusting the L, then converting
back to RGB. Without any knowledge beyond what you've said, this sounds
like exactly like what I'm trying to do. It also sounds easier than mixing
pattern grey shades. I'm not sure, but at least now I know where to look
and where to possible point further questions.

Thanks.
-Jeremy

Peter T wrote:
Hi Jeremy

I know I can read the RGB value of a color in VBA.

If you use customized palette colours be sure to read the RGB of the
color index and not the RGB of some format. Otherwise you may return
the default palette colour rather than the actual colour (depends on
other factors related in your overall code).

Is there a consistent way to do a calculation on those values to
visually return the same color but lighter (dimmer)?

Depending on what you mean by making lighter/darker it probably
involves converting RGB to HSL, adjusting the L value +/- then
reconverting the new HSL to RGB. Then perhaps customizing one of the
palette colours with the new RGB.

But if all you want to do is format a similar colour lighter/darker a
crude way is mixing pattern "grey" shades with pattern colour white
through grey to black. If you want to go further over 2000 unique
colours can be "simulated" using pattern shades with the default
palette of 46 unique colours (56-10 duplicates).

Regards,
Peter T

"Jeremy Gollehon" wrote in message
...
I know I can read the RGB value of a color in VBA.

Is there a consistent way to do a calculation on those values to
visually return the same color but lighter (dimmer)?

Thanks,
Jeremy