Is there an equivalent "color index" for "system color" in VBA?

e.g. I'm thinking to color cells the same as a default button face color
(i.e. system color &H8000000F&) without having to create a shape or button,
but it doesn't seem to be possible from this very useful link I found in this
forum sometime ago...Is this right?

http://www.mvps.org/dmcritchie/excel/colors.htm

Sam

hi,
i don't have 2007. 2002 here. but the RGB equivilent to the button color is
RGB(220,220,220). how you would adjust that in 2007, i don't know.
in 2002, i adust the colors using the RGB scale.

regards
FSt1

"Sam Kuo" wrote:

> Is there an equivalent "color index" for "system color" in VBA?
>
> e.g. I'm thinking to color cells the same as a default button face color
> (i.e. system color &H8000000F&) without having to create a shape or button,
> but it doesn't seem to be possible from this very useful link I found in this
> forum sometime ago...Is this right?
>
> http://www.mvps.org/dmcritchie/excel/colors.htm
>
> Sam

Thanks FSt1. But RGB(220,220,220) appears to be "Grey-25%" in the standard
Excel color platte, not Button Face color??

I'm using Excel 2003.

Sam

"FSt1" wrote:

> hi,
> i don't have 2007. 2002 here. but the RGB equivilent to the button color is
> RGB(220,220,220). how you would adjust that in 2007, i don't know.
> in 2002, i adust the colors using the RGB scale.
>
> regards
> FSt1
>
> "Sam Kuo" wrote:
>
> > Is there an equivalent "color index" for "system color" in VBA?
> >
> > e.g. I'm thinking to color cells the same as a default button face color
> > (i.e. system color &H8000000F&) without having to create a shape or button,
> > but it doesn't seem to be possible from this very useful link I found in this
> > forum sometime ago...Is this right?
> >
> > http://www.mvps.org/dmcritchie/excel/colors.htm
> >
> > Sam

You need to use some VBA or similar magic to convert from the windows system
colors to RGB that you can apply to objects in Excel. Here's a couple
relevant links:

http://www.vbaccelerator.com/home/VB...OR/article.asp
http://www.vbaccelerator.com/tips/vba0018.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sam Kuo" <(E-Mail Removed)> wrote in message
news:8153B988-AAC6-4879-BD74-(E-Mail Removed)...
> Thanks FSt1. But RGB(220,220,220) appears to be "Grey-25%" in the standard
> Excel color platte, not Button Face color??
>
> I'm using Excel 2003.
>
> Sam
>
> "FSt1" wrote:
>
>> hi,
>> i don't have 2007. 2002 here. but the RGB equivilent to the button color
>> is
>> RGB(220,220,220). how you would adjust that in 2007, i don't know.
>> in 2002, i adust the colors using the RGB scale.
>>
>> regards
>> FSt1
>>
>> "Sam Kuo" wrote:
>>
>> > Is there an equivalent "color index" for "system color" in VBA?
>> >
>> > e.g. I'm thinking to color cells the same as a default button face
>> > color
>> > (i.e. system color &H8000000F&) without having to create a shape or
>> > button,
>> > but it doesn't seem to be possible from this very useful link I found
>> > in this
>> > forum sometime ago...Is this right?
>> >
>> > http://www.mvps.org/dmcritchie/excel/colors.htm
>> >
>> > Sam

I used the color detector downloaded from David McRitchie's website mentioned
in the last post.
It showed that the Button Face color is RGB(236,233,216). so I then run the
macro using this RGB setting, but the outcome cell color becomes
RGB(255,255,204) instead according to the same Color Detector used... Any
idea?

Thanks
Sam

"FSt1" wrote:

> hi,
> i don't have 2007. 2002 here. but the RGB equivilent to the button color is
> RGB(220,220,220). how you would adjust that in 2007, i don't know.
> in 2002, i adust the colors using the RGB scale.
>
> regards
> FSt1
>
> "Sam Kuo" wrote:
>
> > Is there an equivalent "color index" for "system color" in VBA?
> >
> > e.g. I'm thinking to color cells the same as a default button face color
> > (i.e. system color &H8000000F&) without having to create a shape or button,
> > but it doesn't seem to be possible from this very useful link I found in this
> > forum sometime ago...Is this right?
> >
> > http://www.mvps.org/dmcritchie/excel/colors.htm
> >
> > Sam

Thanks Jon. I followed what the link said, but get a "run-time error 438"
after clicking the command button...Any idea?

Error occurs at this line:
Label1.BackColor = Combo1.ItemData(Combo1.ListIndex)



"Jon Peltier" wrote:

> You need to use some VBA or similar magic to convert from the windows system
> colors to RGB that you can apply to objects in Excel. Here's a couple
> relevant links:
>
> http://www.vbaccelerator.com/home/VB...OR/article.asp
> http://www.vbaccelerator.com/tips/vba0018.htm
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
>
>
> "Sam Kuo" <(E-Mail Removed)> wrote in message
> news:8153B988-AAC6-4879-BD74-(E-Mail Removed)...
> > Thanks FSt1. But RGB(220,220,220) appears to be "Grey-25%" in the standard
> > Excel color platte, not Button Face color??
> >
> > I'm using Excel 2003.
> >
> > Sam
> >
> > "FSt1" wrote:
> >
> >> hi,
> >> i don't have 2007. 2002 here. but the RGB equivilent to the button color
> >> is
> >> RGB(220,220,220). how you would adjust that in 2007, i don't know.
> >> in 2002, i adust the colors using the RGB scale.
> >>
> >> regards
> >> FSt1
> >>
> >> "Sam Kuo" wrote:
> >>
> >> > Is there an equivalent "color index" for "system color" in VBA?
> >> >
> >> > e.g. I'm thinking to color cells the same as a default button face
> >> > color
> >> > (i.e. system color &H8000000F&) without having to create a shape or
> >> > button,
> >> > but it doesn't seem to be possible from this very useful link I found
> >> > in this
> >> > forum sometime ago...Is this right?
> >> >
> >> > http://www.mvps.org/dmcritchie/excel/colors.htm
> >> >
> >> > Sam
>
>
>

Looks like Excel only allows limited color as the cells interior color. Any
other RGB triple will just be mapped to the nearest color index.

http://support.microsoft.com/?id=170781

Thanks FSt1 and Jon for your knid help :-)


"FSt1" wrote:

> hi,
> i don't have 2007. 2002 here. but the RGB equivilent to the button color is
> RGB(220,220,220). how you would adjust that in 2007, i don't know.
> in 2002, i adust the colors using the RGB scale.
>
> regards
> FSt1
>
> "Sam Kuo" wrote:
>
> > Is there an equivalent "color index" for "system color" in VBA?
> >
> > e.g. I'm thinking to color cells the same as a default button face color
> > (i.e. system color &H8000000F&) without having to create a shape or button,
> > but it doesn't seem to be possible from this very useful link I found in this
> > forum sometime ago...Is this right?
> >
> > http://www.mvps.org/dmcritchie/excel/colors.htm
> >
> > Sam

I had a similar question a while back when I was trying to format
pivot tables with VBA.
Excel shows the button face color in a cell in the header of a pivot
table when the pivot table is originally created. When you apply a
std. cell color to the header cells of the pivot table there seems to
be no way to restore the button face color for these cells other than
completely resetting the whole pivot table formatting by setting the
pivottable property .PreserveFormatting= false. I've spent some time
exploring the object model of the pivot table to see whether I can
find anything that defines this button face color for the header
cells, or that can be used directly to reset only the interior color
of these cells instead of resetting the format of the whole pivot
table. I couldn't find anything. Does someone know a better way???

And by the way, you can precisely adjust a cell color by modifying the
RGB setting of a certain colorindex via "Tools"-> Options -> Color ->
Modify -> Custom.

or via VBA

ActiveWorkbook.Colors(15) = RGB(236,233,216)