Color constants in VBA

M

Mark Tangard

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.BackgroundPatternColorIndex = wdDarkBlue

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

TIA
 
J

Jim Cone

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
 
M

Mark Tangard

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
 
W

Wouter

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
 
K

keepITcool

Mark Tangard wrote in said:
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?
Click to expand...

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
 
C

CST

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.
 
G

Gord Dibben

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
 
M

Mark Tangard

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
 
M

Mark Tangard

keepITcool said:
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
Click to expand...

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
 
M

Mark Tangard

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

MT

Gord said:
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

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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

FWT Newsletter - Weekly - November 3, 2003 2

Top