Color constants in VBA

Discussion in 'Microsoft Excel Programming' started by Mark Tangard, Jul 14, 2004.

  1. Mark Tangard

    Mark Tangard Guest

    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
     
    Mark Tangard, Jul 14, 2004
    #1
    1. Advertisements

  2. Mark Tangard

    Jim Cone Guest

    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:...
    > 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
     
    Jim Cone, Jul 14, 2004
    #2
    1. Advertisements

  3. Mark Tangard

    Mark Tangard Guest

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

    >
    >
     
    Mark Tangard, Jul 19, 2004
    #3
  4. Mark Tangard

    Mark Tangard Guest

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

    >>
    >>
    >>

    >
     
    Mark Tangard, Jul 20, 2004
    #4
  5. Mark Tangard

    Wouter Guest

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

    > >
     
    Wouter, Jul 20, 2004
    #5
  6. Mark Tangard

    keepITcool Guest

    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
     
    keepITcool, Jul 20, 2004
    #6
  7. Mark Tangard

    CST Guest

    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:<>...
    > 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
     
    CST, Jul 20, 2004
    #7
  8. Mark Tangard

    Gord Dibben Guest

    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
     
    Gord Dibben, Jul 20, 2004
    #8
  9. Mark Tangard

    Mark Tangard Guest

    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:<>...
    >
    >>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, Jul 22, 2004
    #9
  10. Mark Tangard

    Mark Tangard Guest

    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
     
    Mark Tangard, Jul 22, 2004
    #10
  11. Mark Tangard

    Mark Tangard Guest

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

    >
    >
     
    Mark Tangard, Jul 22, 2004
    #11
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    List of VBA Functions and Constants

    Guest, Jun 9, 2004, in forum: Microsoft Excel Programming
    Replies:
    4
    Views:
    7,578
  2. mhax

    Color color! Cell color!

    mhax, Aug 2, 2006, in forum: Microsoft Excel Programming
    Replies:
    5
    Views:
    1,221
  3. Guest

    ??? How to get list of Color constants

    Guest, Sep 13, 2006, in forum: Microsoft Excel Programming
    Replies:
    6
    Views:
    1,418
    Guest
    Sep 13, 2006
  4. Walter Briscoe

    Can VBA use array constants

    Walter Briscoe, Mar 30, 2009, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    767
    Peter T
    Mar 30, 2009
  5. Ryan H

    RE: Color Codes: Different color with same color Index

    Ryan H, Feb 5, 2010, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    395
    Ryan H
    Feb 5, 2010
Loading...

Share This Page