Excel Object Model: Color properties

themeColor

The value of the themeColor property can be set to one of the following xlThemeColor enum values:

`xlThemeColorAccent1`	5
`xlThemeColorAccent2`	6
`xlThemeColorAccent3`	7
`xlThemeColorAccent4`	8
`xlThemeColorAccent5`	9
`xlThemeColorAccent6`	10
`xlThemeColorDark1`	1
`xlThemeColorDark2`	3
`xlThemeColorFollowedHyperlink`	12
`xlThemeColorHyperlink`	11
`xlThemeColorLight1`	2
`xlThemeColorLight2`	4

Typically, using the property themeColor goes hand in hand with also using tintAndShade.

Note that the value of themeColor needs to be assigned before the value of tintAndShade is assigned.

tintAndShade

tintAndShade is a double with a value between -1.0 and 1.0 where the value influences the color from black (-1.0) over neutral (0) to white (1.0).

The value of tintAndShade affects theme colors and non-theme colors.

The following example demonstrates the combination of themeColor and tintAndShade:

option explicit

sub main() ' {

    dim r as long
    r = 1

    cells(r,  2) = "-1.0"
    cells(r,  3) = "-0.8"
    cells(r,  4) = "-0.6"
    cells(r,  5) = "-0.4"
    cells(r,  6) = "-0.2"
    cells(r,  7) = " 0.0"
    cells(r,  8) = " 0.2"
    cells(r,  9) = " 0.4"
    cells(r, 10) = " 0.6"
    cells(r, 11) = " 0.8"
    cells(r, 12) = " 1.0"

    range(cells(r,2), cells(r,12)).horizontalAlignment  = xlCenter
    range(columns(2), columns(12)).columnWidth = 5

    drawThemeColor xlThemeColorAccent1           , "xlThemeColorAccent1"           , r
    drawThemeColor xlThemeColorAccent2           , "xlThemeColorAccent2"           , r
    drawThemeColor xlThemeColorAccent3           , "xlThemeColorAccent3"           , r
    drawThemeColor xlThemeColorAccent4           , "xlThemeColorAccent4"           , r
    drawThemeColor xlThemeColorAccent5           , "xlThemeColorAccent5"           , r
    drawThemeColor xlThemeColorAccent6           , "xlThemeColorAccent6"           , r
    drawThemeColor xlThemeColorDark1             , "xlThemeColorDark1"             , r
    drawThemeColor xlThemeColorDark2             , "xlThemeColorDark2"             , r
    drawThemeColor xlThemeColorFollowedHyperlink , "xlThemeColorFollowedHyperlink" , r
    drawThemeColor xlThemeColorHyperlink         , "xlThemeColorHyperlink"         , r
    drawThemeColor xlThemeColorLight1            , "xlThemeColorLight1"            , r
    drawThemeColor xlThemeColorLight2            , "xlThemeColorLight2"            , r

    columns(1).autoFit

    activeWorkbook.saved = true

end sub ' }


sub drawThemeColor(col as xlThemeColor, nam as string, byRef r as long) ' {

    r = r + 1

    cells(r, 1) = nam

    range(cells(r,2), cells(r,12)).interior.themeColor = col

    cells(r, 2).interior.tintAndShade =  -1.0
    cells(r, 3).interior.tintAndShade =  -0.8
    cells(r, 4).interior.tintAndShade =  -0.6
    cells(r, 5).interior.tintAndShade =  -0.4
    cells(r, 6).interior.tintAndShade =  -0.2
    cells(r, 7).interior.tintAndShade =   0.0
    cells(r, 8).interior.tintAndShade =   0.2
    cells(r, 9).interior.tintAndShade =   0.4
    cells(r,10).interior.tintAndShade =   0.6
    cells(r,11).interior.tintAndShade =   0.8
    cells(r,12).interior.tintAndShade =   1.0


end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/_colors/themeColor-tintAndShade.bas

colorIndex

colorIndex is an integer (long?) hat can be set to a value between 1 and 56.

The following example creates a grid for all values of colorIndex:

option explicit

const nofColorIndices = 56

sub main() ' {

    dim colorIndex as long
    for colorIndex = 1 to nofColorIndices ' {

        dim row as long
        dim col as long

        row = (colorIndex-1) \   8 + 1
        col = (colorIndex-1) mod 8 + 1

        with cells(row, col) ' {

             .value               =  colorIndex
             .interior.colorIndex =  colorIndex
             .font.colorIndex     = (colorIndex + 11) mod nofColorIndices

        end with ' }

    next colorIndex ' }

    range(columns(1), columns(8)).columnWidth = 6
    range(rows   (1), rows   (8)).rowHeight   = 38

    with range(cells(1,1), cells(nofColorIndices \ 8, 8))

        .font.size           = 20
        .font.bold           = true
        .horizontalAlignment = xlCenter
        .verticalAlignment   = xlCenter

    end with

    activeWorkbook.saved = true

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/_colors/colorIndex.bas

Excel Object Model: Color properties

themeColor

tintAndShade

colorIndex

color

See also