PC Review


Reply
Thread Tools Rating: Thread Rating: 1 votes, 2.00 average.

Color constants in VBA

 
 
Mark Tangard
Guest
Posts: n/a
 
      14th Jul 2004
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
--
Mark Tangard, Microsoft Word MVP
"Life is nothing if you're not obsessed." --John Waters

 
Reply With Quote
 
 
 
 
Jim Cone
Guest
Posts: n/a
 
      14th Jul 2004
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" <Mark@RemoveThisToReply_Tangard.com> wrote in message news:(E-Mail Removed)...
> 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
> Mark Tangard, Microsoft Word MVP
> "Life is nothing if you're not obsessed." --John Waters


 
Reply With Quote
 
 
 
 
Mark Tangard
Guest
Posts: n/a
 
      19th Jul 2004
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" <Mark@RemoveThisToReply_Tangard.com> wrote in message news:(E-Mail Removed)...
>
>>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
>>Mark Tangard, Microsoft Word MVP
>>"Life is nothing if you're not obsessed." --John Waters

>
>


 
Reply With Quote
 
Mark Tangard
Guest
Posts: n/a
 
      20th Jul 2004
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" <Mark@RemoveThisToReply_Tangard.com> wrote in message
>> news:(E-Mail Removed)...
>>
>>> 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
>>> Mark Tangard, Microsoft Word MVP
>>> "Life is nothing if you're not obsessed." --John Waters

>>
>>
>>

>


 
Reply With Quote
 
Wouter
Guest
Posts: n/a
 
      20th Jul 2004
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 <Mark@RemoveThisToReply_Tangard.com> wrote in message news:<(E-Mail Removed)>...
> 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" <Mark@RemoveThisToReply_Tangard.com> wrote in message
> >> news:(E-Mail Removed)...
> >>
> >>> 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
> >>> Mark Tangard, Microsoft Word MVP
> >>> "Life is nothing if you're not obsessed." --John Waters
> >>
> >>
> >>

> >

 
Reply With Quote
 
keepITcool
Guest
Posts: n/a
 
      20th Jul 2004
Mark Tangard wrote in <(E-Mail Removed)>:

> 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
 
Reply With Quote
 
CST
Guest
Posts: n/a
 
      20th Jul 2004
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 <Mark@RemoveThisToReply_Tangard.com> wrote in message news:<(E-Mail Removed)>...
> 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

 
Reply With Quote
 
Gord Dibben
Guest
Posts: n/a
 
      20th Jul 2004
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
<Mark@RemoveThisToReply_Tangard.com> 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


 
Reply With Quote
 
Mark Tangard
Guest
Posts: n/a
 
      22nd Jul 2004
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 <Mark@RemoveThisToReply_Tangard.com> wrote in message news:<(E-Mail Removed)>...
>
>>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


 
Reply With Quote
 
Mark Tangard
Guest
Posts: n/a
 
      22nd Jul 2004


keepITcool wrote:

> Mark Tangard wrote in <(E-Mail Removed)>:
>
>
>>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

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
color transition (color changes from dark color to light color) color transition Microsoft Powerpoint 2 21st Nov 2009 01:21 PM
??? How to get list of Color constants =?Utf-8?B?SmF5bGlu?= Microsoft Excel Programming 6 13th Sep 2006 08:36 PM
How to get a dump of Fill Color & Font VBA Constants? EagleOne Microsoft Excel Misc 3 17th Aug 2006 08:17 PM
List of VBA Functions and Constants =?Utf-8?B?U3VwZXJKYXM=?= Microsoft Excel Programming 4 10th Jun 2004 03:58 AM
integer constants used with string constants Eric Newton Microsoft Dot NET Framework 3 14th Jan 2004 06:05 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 01:18 AM.