Color constants in VBA
 

Are there no color constants in Excel VBA for fonts, interior shading,
etc., other than the usual None and Auto? I don't see a list in the
Help, and I've tried all the usual xl- and vb- prefixed combinations
(separating prefix from color name with Color, ColorIndex, or nothing).
Does Excel not have the semi-English constants like these in Word:

Selection.Font.Color = wdColorDarkBlue
Selection.Font.ColorIndex = wdDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColor = wdColorDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColorI ndex = wdDarkBlue

I was sure I'd seen them someplace but noooooo.....?

TIA
--
Mark Tangard, Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters

Color constants in VBA
 

Mark,

From: Color Constants in Excel VBA help...

Constant Value

vbBlack 0x0
vbRed 0xFF
vbGreen 0xFF00
vbYellow 0xFFFF
vbBlue 0xFF0000
vbMagenta 0xFF00FF
vbCyan 0xFFFF00
vbWhite 0xFFFFFF

Regards,
Jim Cone
San Francisco, CA

"Mark Tangard" wrote in message ...
Are there no color constants in Excel VBA for fonts, interior shading,
etc., other than the usual None and Auto? I don't see a list in the
Help, and I've tried all the usual xl- and vb- prefixed combinations
(separating prefix from color name with Color, ColorIndex, or nothing).
Does Excel not have the semi-English constants like these in Word:
Selection.Font.Color = wdColorDarkBlue
Selection.Font.ColorIndex = wdDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColor = wdColorDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColorI ndex = wdDarkBlue
I was sure I'd seen them someplace but noooooo.....?
TIA
Mark Tangard, Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters

Color constants in VBA
 

Thank you Jim. Don't know why this never came up for me.

MT

Jim Cone wrote:
Mark,

From: Color Constants in Excel VBA help...

Constant Value

vbBlack 0x0
vbRed 0xFF
vbGreen 0xFF00
vbYellow 0xFFFF
vbBlue 0xFF0000
vbMagenta 0xFF00FF
vbCyan 0xFFFF00
vbWhite 0xFFFFFF

Regards,
Jim Cone
San Francisco, CA

"Mark Tangard" wrote in message ...

Are there no color constants in Excel VBA for fonts, interior shading,
etc., other than the usual None and Auto? I don't see a list in the
Help, and I've tried all the usual xl- and vb- prefixed combinations
(separating prefix from color name with Color, ColorIndex, or nothing).
Does Excel not have the semi-English constants like these in Word:
Selection.Font.Color = wdColorDarkBlue
Selection.Font.ColorIndex = wdDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColo r = wdColorDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColo rIndex = wdDarkBlue
I was sure I'd seen them someplace but noooooo.....?
TIA
Mark Tangard, Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters

Color constants in VBA
 

Hm. Actually it still doesn't come up in my VBA Help (XL 2000)....

These do work; but I was hoping to find the constants for all 40 of the
colors that can be applied to a cell interior from the toolbar dropdown.
Are these not all available as VBA constants?

TIA
--
Mark Tangard
"Life is nothing if you're not obsessed." --John Waters


Mark Tangard wrote:
Thank you Jim. Don't know why this never came up for me.

MT

Jim Cone wrote:

Mark,

From: Color Constants in Excel VBA help...

Constant Value
vbBlack 0x0
vbRed 0xFF
vbGreen 0xFF00
vbYellow 0xFFFF vbBlue 0xFF0000
vbMagenta 0xFF00FF
vbCyan 0xFFFF00
vbWhite 0xFFFFFF
Regards,
Jim Cone
San Francisco, CA

"Mark Tangard" wrote in message
...

Are there no color constants in Excel VBA for fonts, interior
shading, etc., other than the usual None and Auto? I don't see a
list in the Help, and I've tried all the usual xl- and vb- prefixed
combinations (separating prefix from color name with Color,
ColorIndex, or nothing). Does Excel not have the semi-English
constants like these in Word:
Selection.Font.Color = wdColorDarkBlue
Selection.Font.ColorIndex = wdDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColor = wdColorDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColorI ndex = wdDarkBlue
I was sure I'd seen them someplace but noooooo.....?
TIA
Mark Tangard, Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters

Color constants in VBA
 

Hi Mark,

To see the help text about the possible colorconstants:

Copy this procedure to VBE

Sub AnOthorColor()
ActiveCell.Font.Color = vbGreen
End Sub

position your cursor over vbGreen in and hit [F1]

Greeting,

Wouter


Mark Tangard wrote in message ...
Hm. Actually it still doesn't come up in my VBA Help (XL 2000)....

These do work; but I was hoping to find the constants for all 40 of the
colors that can be applied to a cell interior from the toolbar dropdown.
Are these not all available as VBA constants?

TIA
--
Mark Tangard
"Life is nothing if you're not obsessed." --John Waters


Mark Tangard wrote:
Thank you Jim. Don't know why this never came up for me.

MT

Jim Cone wrote:

Mark,

From: Color Constants in Excel VBA help...

Constant Value
vbBlack 0x0
vbRed 0xFF
vbGreen 0xFF00
vbYellow 0xFFFF vbBlue 0xFF0000
vbMagenta 0xFF00FF
vbCyan 0xFFFF00
vbWhite 0xFFFFFF
Regards,
Jim Cone
San Francisco, CA

"Mark Tangard" wrote in message
...

Are there no color constants in Excel VBA for fonts, interior
shading, etc., other than the usual None and Auto? I don't see a
list in the Help, and I've tried all the usual xl- and vb- prefixed
combinations (separating prefix from color name with Color,
ColorIndex, or nothing). Does Excel not have the semi-English
constants like these in Word:
Selection.Font.Color = wdColorDarkBlue
Selection.Font.ColorIndex = wdDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColor = wdColorDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColorI ndex = wdDarkBlue
I was sure I'd seen them someplace but noooooo.....?
TIA
Mark Tangard, Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters

Color constants in VBA
 

Mark Tangard wrote in :

Hm. Actually it still doesn't come up in my VBA Help (XL 2000)....

These do work; but I was hoping to find the constants for all 40 of
the colors that can be applied to a cell interior from the toolbar
dropdown. Are these not all available as VBA constants?


Dont get confused between VBA color constants
and EXCEL COLORINDEX and COLORS properties

When you speak of 40 colors you're referring to the COLORINDEX

each workbook has a property called COLORS.
this is in fact an 1x56 array of doubles representing RGB values.

the colorindex is a pointer to an element in the COLORS array.

note hex representation is BRG not RGB..
activecell.Interior.Color=&Hff0000 '<= BLUE 255,Green0,Red0
?activecell.Interior.ColorIndex
5

dim ci,co
co=activeworkbook.colors
for each ci in co
debug.print ci, hex(ci)
next


hth



--
keepITcool

| www.XLsupport.com | keepITcool chello nl | amsterdam

Color constants in VBA
 

Const Color
1 Black
5 Blue
53 Brown
11 Dark Blue
51 Dark Green
9 Dark Red
15 Gray
10 Green
37 Light Blue
35 Light Green
45 Light Orange
46 Orange
7 Pink
13 Purple
3 Red
2 White

Don't know if this helps or not.

Mark Tangard wrote in message ...
Are there no color constants in Excel VBA for fonts, interior shading,
etc., other than the usual None and Auto? I don't see a list in the
Help, and I've tried all the usual xl- and vb- prefixed combinations
(separating prefix from color name with Color, ColorIndex, or nothing).
Does Excel not have the semi-English constants like these in Word:

Selection.Font.Color = wdColorDarkBlue
Selection.Font.ColorIndex = wdDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColor = wdColorDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColorI ndex = wdDarkBlue

I was sure I'd seen them someplace but noooooo.....?

TIA

Color constants in VBA
 

Mark

Couple of macros for colors listing.

Sub colors56()
'David McRitchie
'57 colors, 0 to 56
'needs ATP loaded
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
Dim str0 As String, Str As String
Sheets.Add
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 1).Value = "[Color " & i & "]"
Cells(i + 1, 2).Font.ColorIndex = i
Cells(i + 1, 2).Value = "[Color " & i & "]"
str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6)
'Excel shows nibbles in reverse order so make it as RGB
Str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
'generating 2 columns in the HTML table
Cells(i + 1, 3) = "#" & Str & "#" & Str & ""
Cells(i + 1, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)"
Cells(i + 1, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)"
Cells(i + 1, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)"
Cells(i + 1, 7) = "[Color " & i & ")"
Next i
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Sub ListColorIndexes()
'John Walkenbach
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx))
Cells(Ndx, 3).Value = Ndx
Next Ndx
End Sub

Gord Dibben Excel MVP

On Mon, 19 Jul 2004 19:55:30 -0700, Mark Tangard
wrote:

Hm. Actually it still doesn't come up in my VBA Help (XL 2000)....

These do work; but I was hoping to find the constants for all 40 of the
colors that can be applied to a cell interior from the toolbar dropdown.
Are these not all available as VBA constants?

TIA

Color constants in VBA
 

Hi. Yes, thanks, it does, since it has those other 'specialty' colors I
was looking for (and since it looks like I'll have to keep this chart on
my desk!).

Strange that they're not available in an easy-to-guess or AutoListed
constant, as in Word. (I don't code much in Excel, but an awful lot of
my Excel macros & UFs end up needing to color things...)

Thanks again.

MT

CST wrote:
Const Color
1 Black
5 Blue
53 Brown
11 Dark Blue
51 Dark Green
9 Dark Red
15 Gray
10 Green
37 Light Blue
35 Light Green
45 Light Orange
46 Orange
7 Pink
13 Purple
3 Red
2 White

Don't know if this helps or not.

Mark Tangard wrote in message ...

Are there no color constants in Excel VBA for fonts, interior shading,
etc., other than the usual None and Auto? I don't see a list in the
Help, and I've tried all the usual xl- and vb- prefixed combinations
(separating prefix from color name with Color, ColorIndex, or nothing).
Does Excel not have the semi-English constants like these in Word:

Selection.Font.Color = wdColorDarkBlue
Selection.Font.ColorIndex = wdDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColo r = wdColorDarkBlue
Selection.Cells(1).Shading.BackgroundPatternColo rIndex = wdDarkBlue

I was sure I'd seen them someplace but noooooo.....?

TIA

Color constants in VBA
 

keepITcool wrote:

Mark Tangard wrote in :


Hm. Actually it still doesn't come up in my VBA Help (XL 2000)....

These do work; but I was hoping to find the constants for all 40 of
the colors that can be applied to a cell interior from the toolbar
dropdown. Are these not all available as VBA constants?



Dont get confused between VBA color constants
and EXCEL COLORINDEX and COLORS properties

When you speak of 40 colors you're referring to the COLORINDEX

each workbook has a property called COLORS.
this is in fact an 1x56 array of doubles representing RGB values.

the colorindex is a pointer to an element in the COLORS array.

note hex representation is BRG not RGB..
activecell.Interior.Color=&Hff0000 '<= BLUE 255,Green0,Red0
?activecell.Interior.ColorIndex
5

dim ci,co
co=activeworkbook.colors
for each ci in co
debug.print ci, hex(ci)
next


hth

OK, thanks, that clarifies. It's a shame they're not available in
text-mnemonic constants, as in Word. Guess I'll be doing a lot of
memorizing....

MT

Color constants in VBA
 

Thank you Gord, this may be the handiest of all the solutions.

MT

Gord Dibben wrote:

Mark

Couple of macros for colors listing.

Sub colors56()
'David McRitchie
'57 colors, 0 to 56
'needs ATP loaded
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
Dim str0 As String, Str As String
Sheets.Add
For i = 0 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 1).Value = "[Color " & i & "]"
Cells(i + 1, 2).Font.ColorIndex = i
Cells(i + 1, 2).Value = "[Color " & i & "]"
str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6)
'Excel shows nibbles in reverse order so make it as RGB
Str = Right(str0, 2) & Mid(str0, 3, 2) & Left(str0, 2)
'generating 2 columns in the HTML table
Cells(i + 1, 3) = "#" & Str & "#" & Str & ""
Cells(i + 1, 4).Formula = "=Hex2dec(""" & Right(str0, 2) & """)"
Cells(i + 1, 5).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)"
Cells(i + 1, 6).Formula = "=Hex2dec(""" & Left(str0, 2) & """)"
Cells(i + 1, 7) = "[Color " & i & ")"
Next i
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Sub ListColorIndexes()
'John Walkenbach
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx))
Cells(Ndx, 3).Value = Ndx
Next Ndx
End Sub

Gord Dibben Excel MVP

On Mon, 19 Jul 2004 19:55:30 -0700, Mark Tangard
wrote:


Hm. Actually it still doesn't come up in my VBA Help (XL 2000)....

These do work; but I was hoping to find the constants for all 40 of the
colors that can be applied to a cell interior from the toolbar dropdown.
Are these not all available as VBA constants?

TIA