Damn ColorIndex, I want to set _Color

[Agoston Bejo]

Hi,
is there a way to programmatically change the background color of a cell
using Excel ActiveX object? Every example on the internet I have found uses
the ColorIndex attribute and hardwired numbers, which gets on my nerves.
I have tried to set the Range.Interior.Color attribute with 'Red', then with
'RGB(15,52,20)' then with '=RGB(16,78,60)' but I constantly get an error
message that says: "Unable to set the Color property of the Interior class".
Note that I'm using the Excel ActiveX objects in JavaScript, not VBA.
Even if I have to use the ColorIndex property, I don't like the idea that I
have to determine the actual colors corresponding to the ColorIndex values
by experimenting. (There's no related constants available in JavaScript, as
far as I know.)

Please help!

 

[David McRitchie]

Hi Agoston,
You'd be a lot less happy if there were only 16 colors.

In Excel you are limited to 56 colors in a workbook.
You can change those colors but I think most people for
consistency would just put up with the current colorindex
values rather than introducing incompatibilities.

When you use an RGB color, Excel will try to match that
color with a colorindex value and if you are critical you will
find that Excel does an awful job at doing even that.

The only way to guarantee your color is by using the
colorindex value. It may not be the most desirable color
but it will be the color from you color palette, so you should
know what you are getting.

Color Palette and the 56 Excel ColorIndex Colors
http://www.mvps.org/dmcritchie/excel/colors.htm

If you do change your colors not that the yellow is also
used for highlighting, so suggest you don't change that one.

 

[Tushar Mehta]

How does one control an ActiveX object from JavaScript?

The following works with VBA:
range("a1").Interior.Color=rgb(255,0,0)

You will have to adapt it to the appropriate JS construct(s).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[JE McGimpsey]

I haven't a clue how you're using Excel ActiveX objects in JavaScript,
but you can control the color (sort of) in VBA by using

Range("A1").Interior.Color = RGB(15,52,20)

However, note that XL only allows 56 colors to be displayed at one time.
All color assignments will be made according to the "closest" color in
the palette.

RGB(15,52,20) = 1324047.

In the default XL palette, however, setting the interior color

Range("A1").Interior.Color = RGB(15,52,20)

results in a "close" interior color:

?range("A1").interior.color
13056

which corresponds to RGB(0,51,0) and Colorindex = 51

You can "fix" this by first setting the workbook's Colors(x) property to
your desired color, then assigning colorindex = x (or color =
rgb(15,52,20)):

ActiveWorkbook.Colors(51) = RGB(15,52,20)
Range("A1").Interior.ColorIndex = 51

?range("A1").Interior.Color
1324047

 

[Earl Kiosterud]

Agoston,

To add to this, since you have to use one of the 56 colors in the pallette
for your workbook, if you want to specify your own RGB values for a color,
you must first modify one of the pallette items (Tools - Options - Color -
Select item in pallette - Modify), then use that pallette item (Colorindex)
in your cell.