Tags: auto, color, constants, excel, fonts, interior, microsoft, msdn, shading, software, vba

Color constants in VBA

On Microsoft » Microsoft Excel

17,363 words with 10 Comments; publish: Fri, 23 May 2008 07:12:00 GMT; (30646.88, « »)

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

All Comments

Leave a comment...

  • 10 Comments
    • 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.excel.todaysummary.com.RemoveThisToReply_Tangard.com> wrote in message news:ujzL5yUaEHA.3708.excel.todaysummary.com.TK2MSFTNGP10.phx.gbl...

      > 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

      #1; Fri, 23 May 2008 07:13:00 GMT
    • 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.excel.todaysummary.com.RemoveThisToReply_Tangard.com> wrote in message news:ujzL5yUaEHA.3708.excel.todaysummary.com.TK2MSFTNGP10.phx.gbl...

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

      >

      #2; Fri, 23 May 2008 07:14:00 GMT
    • 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.excel.todaysummary.com.RemoveThisToReply_Tangard.com> wrote in message

      >> news:ujzL5yUaEHA.3708.excel.todaysummary.com.TK2MSFTNGP10.phx.gbl...

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

      >>

      >

      #3; Fri, 23 May 2008 07:15:00 GMT
    • 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.excel.todaysummary.com.RemoveThisToReply_Tangard.com> wrote in message news:<OKzIKUgbEHA.2816.excel.todaysummary.com.TK2MSFTNGP11.phx.gbl>...

      > 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.excel.todaysummary.com.RemoveThisToReply_Tangard.com> wrote in message

      > >> news:ujzL5yUaEHA.3708.excel.todaysummary.com.TK2MSFTNGP10.phx.gbl...

      > >>

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

      > >>

      > >>

      > >>

      > >

      #4; Fri, 23 May 2008 07:16:00 GMT
    • Mark Tangard wrote in <OKzIKUgbEHA.2816.excel.todaysummary.com.TK2MSFTNGP11.phx.gbl>:

      > 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

      #5; Fri, 23 May 2008 07:17:00 GMT
    • 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.excel.todaysummary.com.RemoveThisToReply_Tangard.com> wrote in message news:<ujzL5yUaEHA.3708.excel.todaysummary.com.TK2MSFTNGP10.phx.gbl>...

      > 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

      #6; Fri, 23 May 2008 07:18:00 GMT
    • 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.excel.todaysummary.com.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

      #7; Fri, 23 May 2008 07:19:00 GMT
    • 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.excel.todaysummary.com.RemoveThisToReply_Tangard.com> wrote in message news:<ujzL5yUaEHA.3708.excel.todaysummary.com.TK2MSFTNGP10.phx.gbl>...

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

      #8; Fri, 23 May 2008 07:20:00 GMT
    • keepITcool wrote:

      > Mark Tangard wrote in <OKzIKUgbEHA.2816.excel.todaysummary.com.TK2MSFTNGP11.phx.gbl>:

      >

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

      #9; Fri, 23 May 2008 07:21:00 GMT
    • 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.excel.todaysummary.com.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

      >

      #10; Fri, 23 May 2008 07:22:00 GMT