Solved: Clearing Color Formats

[YellowLabPro]

Ok guys sorry to hit you w/ these super simple questions, but these go to my fundamentals learning, and looking to push the detail envelope. I recognize that there are other ways to accomplish some of my tasks, but it is totally tied to my complete understanding of the Object Model that I am focused on.

I want to clear the color out of a range, not all the other formats though.
I also want to see if it is possible to do this w/out using the ColorIndex Property and use the Color Property.

Sheets(1).Range("a:i").Interior.Color = xlNone

The preceding line actually turns the range light blue.... :) .

The second question I have is what is the definitive difference between the Color property and the ColorIndex Property?

I could find nothing on the web that provides any details- I only found a book reference
"Microsoft? Excel 2002 Visual Basic? for Applications Step by Step"
and not looking to buy a book for it....:p

[RichardSchollar]

Hi

Default color format is white so you could use:


Sheets(1).Range("a:i").Interior.Color = 16777215

where 16777215 is the Base 10 representation of hexadecimal FFFFFF (ie white in 24-bit colour).

Richard

[Bob Phillips]

Why do you not want to use ColorIndex? It is the appropriate property in most cases. Color is often a waste of time, becuse you pick a coulur of exactly the right hue and saturation for your purposes, and then Excel maps it as closely as possible to the nearest colour palette index (at least prior to 2007).

One advantage of Color is that you can do it in RGB hex, like so

activecell.Interior.Color = &HFF00FF

[YellowLabPro]

Bob,
It is not that I don't want to use ColorIndex.

In my testing as I am cobbling up simple bits of code, I use .Color and use the default vbyellow, green, blue to mark cells for visual confirmation. It is shorter to type and I can see what colors I via the constant color names rather than the numerical values. I have no need for super specific colors at this point.

But today I wanted to remove the color, which my Bit was using .Color, my conventional method, and I could not find a way to do it short of ClearFormats, which was overkill.
So I wanted to be clear in my post/request of how to remove the interior color via the .Color Property so the answers supplied would target this and not opt for .ColorIndex.

As I was searching the OBM it occurred to me to find out what the differences are between the two. Maybe some of the properties are older and have been updated, maybe they are written to handle very specific tasks, and as I begin to understand better how it works I become even more interested in the details and specifics. The Help File does not really do a good job on this one.... that is how I ended up here.

I know it seems like a lot of muck to a lot of people here, I watch the good coders and they have a commanding background of the fundamentals. They don't get trapped because they know what is going on, I on the other hand back myself into corners and have to find complicated ways to get out. I want to build my knowledge of the fundamentals for strong coding practices, and get out of hacking my way out....

So after all that- what the heck is the difference between the two?:rotlaugh:

[Bob Phillips]

You can always create your own Colorindex variables

Here is an enumerated list which you can add to a module and then use the colour constant name.



Public Enum xlColorIndex
xlCIBlack = 1
xlCIWhite = 2
xlCIRed = 3
xlCIBrightGreen = 4
xlCIBlue = 5
xlCIYellow = 6
xlCIPink = 7
xlCITurquoise = 8
xlCIDarkRed = 9
xlCIGreen = 10
xlCIDarkBlue = 11
xlCIDarkYellow = 12
xlCIViolet = 13
xlCITeal = 14
xlCIGray25 = 15
xlCIGray50 = 16
xlCIPeriwinkle = 17
xlCIPlum = 18
xlCIIvory = 19
xlCILightTurquoise = 20
xlCIDarkPurple = 21
xlCICoral = 22
xlCIOceanBlue = 23
xlCIIceBlue = 24
'xlCIDarkBlue = 25
'xlCIPink = 26
'xlCIYellow = 27
'xlCITurquoise = 28
'xlCIViolet = 29
'xlCIDarkRed = 30
'xlCITeal = 31
'xlCIBlue = 32
xlCISkyBlue = 33
xlCILightGreen = 35
xlCILightYellow = 36
xlCIPaleBlue = 37
xlCIRose = 38
xlCILavender = 39
xlCITan = 40
xlCILightBlue = 41
xlCIAqua = 42
xlCILime = 43
xlCIGold = 44
xlCILightOrange = 45
xlCIOrange = 46
xlCIBlueGray = 47
xlCIGray40 = 48
xlCIDarkTeal = 49
xlCISeaGreen = 50
xlCIDarkGreen = 51
xlCIBrown = 53
xlCIIndigo = 55
xlCIGray80 = 56
End Enum

[rory]

Doug,
Colorindex is exactly that - an index into the colour palette. The actual colour produced will depend on what colour has been set at that position. xld has already kindly provided you with the default enumeration for that
Color is the RGB long value and, as has been pointed out, is usually not exactly what you get since most versions of Excel will limit you to the 56 colour options in the colour palette.

[YellowLabPro]

Thank you Bob.

Thanks Rory and Richard.