colorindex = xlnone : color = ???

[erik.van.geit]

Hi, gurus,

simple problem ???
when running this code, you get twice the same msgbox
BUT
the activecell turns white instead of staying "without color"
what's the problem ?

With ActiveCell.Interior
.ColorIndex = xlNone
MsgBox .Color
.Color = .Color
MsgBox .Color
End With

in other words what's the "color" equivalent for
ColorIndex = xlNone
Color = ???

kind regards,
Erik

 

[jindon]

Eric

It is strange though,

If you test like

color=vbwhite

then you will get msgb box telling 16777215, which is exactly the same as
colorindex=xlnone

????

 

[Zack Barresse]

As the constants are all translated to index values anyway, that should be the return value, should it not? I think I'm missing something.. because there is no color equivelant to xlNone, just an index number.

 

[erik.van.geit]

thank you for your contributions, jindon (long time ago in same thread ) and firefytr!

so, you don't know a solution ?

in fact the main problem was someone telling me he couldn't get Interior.ColorIndex to work in Excel97
so I was trying Color instead

do you know if Excel97 supports ColorIndex ?
it would surprise me if not

 

[Zack Barresse]

I don't have it on this machine anymore but afaik it does.

 

[RichardMGreen]

I think the default colour would be something like
colorindex=xlautomatic

 

[Tom Urtis]

Erik - -

There is a difference between Color and ColorIndex.

ColorIndex for no color is 0 or -4142.

Computers do not care about Excel's ColorIndex property in the VBA environment that is familiar to us. That ColorIndex property is a neat and tidy way Microsoft got around the color issue, by providing us with only 56 colors to choose from in the pallette at any one time.

What computers really care about is Color, specifically, the combination of Red, Green, and Blue (RGB) numerical combinations that comprise one of 16777215 colors.

The valid range for a normal RGB color is 0 to 16,777,215. The valid "high end" of each RGB factor is 256. If you multiply 256*256*256, that equals 16777216, which is a number greater by 1 than the 16777215 gotten by your code and therefore exceeds the valid RGB range. To computers, white and nothing share the same RGB and hex values (255, 255, 255 and #FFFFFF#FFFFFF respectively). That is why in your code, a theoretical "nothing" means the same as an actual "least amount of something" to the computer. Because the least amount of something is still more than nothing, the "something" is executed, that being in this case, the color white as our eyes see it in the active cell of your code.

Even revising your code to try to force the RGB's high end of 256 per component will not work because the computer will not allow it; the result will still be white:

With ActiveCell.Interior
.Color = RGB(256, 256, 256)
MsgBox .Color
.Color = .Color
MsgBox .Color
End With


As you might imagine, all these numbers are not coincidental. You may recognize the product of 256*256*256 (16777216) as being the same as the product of rows and columns (256*65536) for count of cells on a worksheet. Computers deal with bytes and base 10 factors that co-exist to help provide the working environment and calculation capabilities that we use in these programs. In this case, the calculation capabilities are RGB values, max of 16777215.

 

[erik.van.geit]

Tom, thank you !

very intresting
nice proof why it was impossible to find "transparant Color"

kind regards,
Erik

 

[Scott Huish]

I may be missing something here, there are 256 values for each of the R,G,B values. (0-255). So VBA does allow all 256 values, because one of the values is 0.