# Color Palette -- the 56 Excel Colors

Location: http://www.mvps.org/dmcritchie/excel/colors.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
Keywords: color, colorindex, colors, colour, colours, palette
[View without Frames]

This page contains some VBA macros and User Defined Functions. If you need assistance to installing either please refer refer to Install a Macro or User Defined Function on my Formula page.

## How the color palette works

The arrangement in XL97 differs from previous releases in that the colors are arranged from darker to lighter colors, but the Color Index values remain the same.

From HELP --> Index --> palette

> Each Microsoft Excel workbook has a palette of 56 colors that you can apply to cells, fonts, gridlines, graphic objects, and fills and lines in a chart. If you are using a color monitor, you can customize the shade and intensity of the colors in the color palette for each workbook.

> When you change a color in the palette, it is changed for any element formatted with the color you changed, throughout the entire workbook. To use the same custom color scheme in a set of workbooks, you can copy the color palette from one workbook to another. For example, you can create a custom color palette that matches your company's logo and image and then copy it into the workbooks used in company presentations. You can also replace the default color palette that Microsoft Excel uses when it creates a new workbook.

### Copy color palettes between workbooks

> HELP --> Wizard --> copy palette
> 1 Open the workbook that contains the color palette you want to copy.
> 2 Switch to the workbook to which you want to copy the color palette.
> 3 On the Tools menu, click Options, and then click the Color tab.
> 4 In the Copy Colors From box, click the workbook that contains the color palette you want to copy.

> Note: I have not had any desire to change my own colors so expect them to be unchanged. Correct interpretation of the 56 colors in the ColorIndex is dependent upon the HTML wizard conversion on ColorIndex numbers. The RGB values definitely match color swatches.

> The names for colors appears to have a wide variance; I am trying to find what Microsoft generally calls them if they are not named in Excel.

> The following colors has been used in Microsoft KB documentation probably for the first 16 colors:
> Black, Blue, Cyan, Green, Magenta, Red, Yellow, White, Dk Blue, Dk Cyan, Dk Green, Dk Magenta, Dk Red, Dk Yellow, Dk Gray, Lt Gray

> More confusion found in MS KB documentation
> 0 - Black, 1 - Blue, 2 - Green, 3 - Cyan, 4 - Red, 5 - Magenta, 6 - Yellow/Brown, 7 - White, 8 - Gray,
> 9 - Bright Blue, A - Bright Green, B - Bright Cyan, C - Bright Red, D - Bright Magenta, E - Bright Yellow, F - Bright White

> I have had to change some colors to match Microsoft usage from what I thought was normal usage. Assignment of name Gray may have to be changed. Color6 and Color27 appear to both be Yellow on my system even after resetting colors (Tools --> Options).

## Color Palette, Excel

To see your 56 colors
In XL95 Help --> index --> Colorindex property
In XL97/XL2000 VBE HELP (Alt+F11, F1) --> index --> ColorIndex property

It would be hard to compare the palettes between XL95 and XL97. The XL95 palette is arranged by index number and the XL97 palette is arranged chromatically.

> This table was NOT generated by the Internet Assistant Wizard for Microsoft Excel. You can find this add-in on "http://www.microsoft.com/msoffice/freestuf/msexcel/index.htm" -->

> Appearance of table redone 2000-12-09 in Excel 2000, I display 32,760 colors, Excel shows only 56 colors at any time. Following are the defaults.

> Text within some cells can be viewed easier by selecting an area with the mouse.

> interior | font | HTML | bgcolor= | Red | Green | Blue | Color
> Black | [Color 1] | #000000 | #000000 | 0 | 0 | 0 | [Black]
> White | [Color 2] | #FFFFFF | #FFFFFF | 255 | 255 | 255 | [White]
> Red | [Color 3] | #FF0000 | #FF0000 | 255 | 0 | 0 | [Red]
> Green | [Color 4] | #00FF00 | #00FF00 | 0 | 255 | 0 | [Green]
> Blue | [Color 5] | #0000FF | #0000FF | 0 | 0 | 255 | [Blue]
> Yellow | [Color 6] | #FFFF00 | #FFFF00 | 255 | 255 | 0 | [Yellow]
> Magenta | [Color 7] | #FF00FF | #FF00FF | 255 | 0 | 255 | [Magenta]
> Cyan | [Color 8] | #00FFFF | #00FFFF | 0 | 255 | 255 | [Cyan]
> [Color 9] | [Color 9] | #800000 | #800000 | 128 | 0 | 0 | [Color 9]
> [Color 10] | [Color 10] | #008000 | #008000 | 0 | 128 | 0 | [Color 10]
> [Color 11] | [Color 11] | #000080 | #000080 | 0 | 0 | 128 | [Color 11]
> [Color 12] | [Color 12] | #808000 | #808000 | 128 | 128 | 0 | [Color 12]
> [Color 13] | [Color 13] | #800080 | #800080 | 128 | 0 | 128 | [Color 13]
> [Color 14] | [Color 14] | #008080 | #008080 | 0 | 128 | 128 | [Color 14]
> [Color 15] | [Color 15] | #C0C0C0 | #C0C0C0 | 192 | 192 | 192 | [Color 15]
> [Color 16] | [Color 16] | #808080 | #808080 | 128 | 128 | 128 | [Color 16]
> [Color 17] | [Color 17] | #9999FF | #9999FF | 153 | 153 | 255 | [Color 17]
> [Color 18] | [Color 18] | #993366 | #993366 | 153 | 51 | 102 | [Color 18]
> [Color 19] | [Color 19] | #FFFFCC | #FFFFCC | 255 | 255 | 204 | [Color 19]
> [Color 20] | [Color 20] | #CCFFFF | #CCFFFF | 204 | 255 | 255 | [Color 20]
> [Color 21] | [Color 21] | #660066 | #660066 | 102 | 0 | 102 | [Color 21]
> [Color 22] | [Color 22] | #FF8080 | #FF8080 | 255 | 128 | 128 | [Color 22]
> [Color 23] | [Color 23] | #0066CC | #0066CC | 0 | 102 | 204 | [Color 23]
> [Color 24] | [Color 24] | #CCCCFF | #CCCCFF | 204 | 204 | 255 | [Color 24]
> [Color 25] | [Color 25] | #000080 | #000080 | 0 | 0 | 128 | [Color 25]
> [Color 26] | [Color 26] | #FF00FF | #FF00FF | 255 | 0 | 255 | [Color 26]
> [Color 27] | [Color 27] | #FFFF00 | #FFFF00 | 255 | 255 | 0 | [Color 27]
> [Color 28] | [Color 28] | #00FFFF | #00FFFF | 0 | 255 | 255 | [Color 28]
> [Color 29] | [Color 29] | #800080 | #800080 | 128 | 0 | 128 | [Color 29]
> [Color 30] | [Color 30] | #800000 | #800000 | 128 | 0 | 0 | [Color 30]
> [Color 31] | [Color 31] | #008080 | #008080 | 0 | 128 | 128 | [Color 31]
> [Color 32] | [Color 32] | #0000FF | #0000FF | 0 | 0 | 255 | [Color 32]
> [Color 33] | [Color 33] | #00CCFF | #00CCFF | 0 | 204 | 255 | [Color 33]
> [Color 34] | [Color 34] | #CCFFFF | #CCFFFF | 204 | 255 | 255 | [Color 34]
> [Color 35] | [Color 35] | #CCFFCC | #CCFFCC | 204 | 255 | 204 | [Color 35]
> [Color 36] | [Color 36] | #FFFF99 | #FFFF99 | 255 | 255 | 153 | [Color 36]
> [Color 37] | [Color 37] | #99CCFF | #99CCFF | 153 | 204 | 255 | [Color 37]
> [Color 38] | [Color 38] | #FF99CC | #FF99CC | 255 | 153 | 204 | [Color 38]
> [Color 39] | [Color 39] | #CC99FF | #CC99FF | 204 | 153 | 255 | [Color 39]
> [Color 40] | [Color 40] | #FFCC99 | #FFCC99 | 255 | 204 | 153 | [Color 40]
> [Color 41] | [Color 41] | #3366FF | #3366FF | 51 | 102 | 255 | [Color 41]
> [Color 42] | [Color 42] | #33CCCC | #33CCCC | 51 | 204 | 204 | [Color 42]
> [Color 43] | [Color 43] | #99CC00 | #99CC00 | 153 | 204 | 0 | [Color 43]
> [Color 44] | [Color 44] | #FFCC00 | #FFCC00 | 255 | 204 | 0 | [Color 44]
> [Color 45] | [Color 45] | #FF9900 | #FF9900 | 255 | 153 | 0 | [Color 45]
> [Color 46] | [Color 46] | #FF6600 | #FF6600 | 255 | 102 | 0 | [Color 46]
> [Color 47] | [Color 47] | #666699 | #666699 | 102 | 102 | 153 | [Color 47]
> [Color 48] | [Color 48] | #969696 | #969696 | 150 | 150 | 150 | [Color 48]
> [Color 49] | [Color 49] | #003366 | #003366 | 0 | 51 | 102 | [Color 49]
> [Color 50] | [Color 50] | #339966 | #339966 | 51 | 153 | 102 | [Color 50]
> [Color 51] | [Color 51] | #003300 | #003300 | 0 | 51 | 0 | [Color 51]
> [Color 52] | [Color 52] | #333300 | #333300 | 51 | 51 | 0 | [Color 52]
> [Color 53] | [Color 53] | #993300 | #993300 | 153 | 51 | 0 | [Color 53]
> [Color 54] | [Color 54] | #993366 | #993366 | 153 | 51 | 102 | [Color 54]
> [Color 55] | [Color 55] | #333399 | #333399 | 51 | 51 | 153 | [Color 55]
> [Color 56] | [Color 56] | #333333 | #333333 | 51 | 51 | 51 | [Color 56]
> Excel only recognizes names for Color 1 through 8 (Black, White, Red, Green, Blue, Yellow, Magenta, and Cyan). The colors 1-16 are widely understood color names from the VGA color palette. The 56 colors names indicated on the Excel color palette (see below) are for descriptive purposes only.

> The above table was created in Excel 2000 with help from the following macro, which includes Worksheet function HEX2DEC. The table was converted to HTML using XL2HTML macro which (at least when done) does not convert embedded HTML code within a cell.

>     Sub colors56()
>     '57 colors, 0 to 56
>       Application.ScreenUpdating = False
>       Application.Calculation = xlCalculationManual   'pre XL97 xlManual
>     Dim i As Long
>     Dim str0 As String, str As String
>     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  'pre XL97 xlAutomatic
>       Application.ScreenUpdating = True
>     End Sub

> ### ColorIndex -- 56 Excel Colors [#56colors]

> [Color 0] | [Color 0] |  | [Color 15] | [Color 15] |  | [Color 30] | [Color 30] |  | [Color 45] | [Color 45]
> [Color 1] | [Color 1] |  | [Color 16] | [Color 16] |  | [Color 31] | [Color 31] |  | [Color 46] | [Color 46]
> [Color 2] | [Color 2] |  | [Color 17] | [Color 17] |  | [Color 32] | [Color 32] |  | [Color 47] | [Color 47]
> [Color 3] | [Color 3] |  | [Color 18] | [Color 18] |  | [Color 33] | [Color 33] |  | [Color 48] | [Color 48]
> [Color 4] | [Color 4] |  | [Color 19] | [Color 19] |  | [Color 34] | [Color 34] |  | [Color 49] | [Color 49]
> [Color 5] | [Color 5] |  | [Color 20] | [Color 20] |  | [Color 35] | [Color 35] |  | [Color 50] | [Color 50]
> [Color 6] | [Color 6] |  | [Color 21] | [Color 21] |  | [Color 36] | [Color 36] |  | [Color 51] | [Color 51]
> [Color 7] | [Color 7] |  | [Color 22] | [Color 22] |  | [Color 37] | [Color 37] |  | [Color 52] | [Color 52]
> [Color 8] | [Color 8] |  | [Color 23] | [Color 23] |  | [Color 38] | [Color 38] |  | [Color 53] | [Color 53]
> [Color 9] | [Color 9] |  | [Color 24] | [Color 24] |  | [Color 39] | [Color 39] |  | [Color 54] | [Color 54]
> [Color 10] | [Color 10] |  | [Color 25] | [Color 25] |  | [Color 40] | [Color 40] |  | [Color 55] | [Color 55]
> [Color 11] | [Color 11] |  | [Color 26] | [Color 26] |  | [Color 41] | [Color 41] |  | [Color 56] | [Color 56]
> [Color 12] | [Color 12] |  | [Color 27] | [Color 27] |  | [Color 42] | [Color 42] |  |  |
> [Color 13] | [Color 13] |  | [Color 28] | [Color 28] |  | [Color 43] | [Color 43] |  |  |
> [Color 14] | [Color 14] |  | [Color 29] | [Color 29] |  | [Color 44] | [Color 44] |  |  |

> 1 | 53 | 52 | 51 | 49 | 55 | 56
> 9 | 46 | 12 | 10 | 5 | 47 | 16
> 3 | 45 | 43 | 50 | 41 | 13 | 48
> 7 | 44 | 6 | 4 | 33 | 54 | 15
> 38 | 40 | 36 | 35 | 37 | 39 | 2
> Black | Brown | Olive Green | Dark Green | Dark Teal | Dark Blue | Gray- 80%
> Dark Red | Orange | Dark Yellow | Green | Teal | Blue | Gray- 50%
> Red | Light Orange | Lime | Sea Green | Aqua | Light Blue | Gray- 40%
> Pink | Gold | Yellow | Bright Green | Turquoise | Sky Blue | Gray- 25%
> Rose | Tan | Light Yellow | Light Green | Light Turquoise | Pale Blue | White

> The colors names indicated on the color palette are for descriptive purposes only. Excel only recognizes names for Color 1 through 8 (Black, White, Red, Green, Blue, Yellow, Magenta, and Cyan).

> Hex equivalents used in HTML

> #000000 | #993300 | #333300 | #003300 | #003366 | #333399 | #333333
> #800000 | #FF6600 | #808000 | #008000 | #0000FF | #666699 | #808080
> #FF0000 | #FF9900 | #99cc00 | #339966 | #3366FF | #800080 | #969696
> #FF00FF | #FFcc00 | #FFFF00 | #00FF00 | #00ccFF | #993366 | #c0c0c0
> #FF99cc | #FFcc99 | #FFFF99 | #ccFFcc | #99ccFF | #cc99FF | #FFFFFF

## Colors in Cell Formatting

The following colors may be used in formatting statements: (see color table above)
black, blue, green, cyan, red, magenta, yellow, white; and, Color1, color2, color3, ..., color56

The parts of the format (unless changed) are:
positive numbers; negative numbers; zero; text

Entry | Formatted | Format -- GetFormat(cell) was used to display Format
-7 | -7.00 | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@
-3 | -3.00 | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@
=-2 | -2 | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@
0 | 0 | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@
4 | 4 | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@
5 | 5.00 | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@
6 | 6.00 | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@
7 | 7.00 | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@
Test | Text:Test | [Blue][>=5]0.00;[Red][<-2]-0.00;[Color 22]General;[magenta]"Text:"@

## Setting Colors in Excel VBA Macros

    ActiveCell.Borders.Color = RGB(255, 0, 0)
    ActiveCell.Borders(xlTop).Color = RGB(0, 0, 255)
    ActiveCell.Borders(xlBottom).ColorIndex = 18

    ActiveCell.Font.ColorIndex = 17
    ActiveCell.Font.Color = RGB(255, 0, 0)
    Selection.Interior.ColorIndex = xlNone  'xlColorIndexAutomatic or xlColorIndexNone
    Selection.Interior.ColorIndex = 5
    Selection.Interior.Color = RGB(200, 250, 200)
    Selection.Interior.Color = &Hc8efac8   'h=Hex,  o=Octal  anyone still use octal
    ActiveCell.Font.Background = {xlAutomatic | xlOpaque | x1Transparent}

    Workbooks("BOOK1.XLS").Worksheets("Sheet1").Activate
    ActiveWindow.GridlineColor = RGB(255,0,0)

## HELP - related information

HELP --> Find --> color --> color --> "Basic number format codes"

HELP --> Index --> colorindex property --> colorindex property

## Coloring Formulas Blue, and remove other font colors

You could assign this to a toolbar button.

       Sub ColorFormulas()    'xl97 up use xlcelltypeformulas
         Cells.FONT.ColorIndex = xlAutomatic
         Selection.SpecialCells(xlFormulas).FONT.ColorIndex = 5
       End Sub

## Coloring Unprotected Cells Blue

Simulate a Lotus 1-2-3 feature to color unprotected cells blue (ref)

    Sub FormatUnprotected()
        For Each Item In Intersect(ActiveSheet.UsedRange, Selection.Cells)
          If Item.Locked = False Then
            Item.Font.colorindex = 32
           End If
        Next
    End Sub

## Changing Font based on interior color and column

Check for interior color of 41 (light blue).

    Sub whiteONblue()
      Application.ScreenUpdating = False
      Application.Calculation = xlCalculationManual   'pre XL97 xlManua
      Dim cell As Range
      '---Range("A3:N100").Select
      For Each cell In Selection
        If cell.Interior.colorindex = 41 And cell.Column = 4 Then
            cell.Font.colorindex = 2 '2=white, 6=yellow
        End If
      Next cell
      Application.Calculation = xlCalculationAutomatic   'pre XL97 xlManua
      Application.ScreenUpdating = False
    End Sub

## Determining Interior Color of Another Cell

     Function showRGB(rcell)
           'Show RGB hex color value of another cell
           showRGB = Right("000000" & Hex(rcell.Interior.Color), 6)
       End Function

       Function showColorIndex(rcell)
          showColorIndex = rcell.Interior.ColorIndex
       End Function

You won't get too far in HTML with the above. Because HTML has the values in RGB order. So the following would be what you need equate to HTML color definitions.

      Function ShowHTMLcolor(xcell) As String
         Dim xColor As String
         xColor = Right("000000" & Hex(xcell.Interior.Color), 6)
         ShowHTMLcolor = "#" & Right(xColor, 2) & Mid(xColor, 3, 2) _
          & Left(xColor, 2)
       End Function

Example: interior colorindex of B28
=ShowColorIndex(B28)

| The shortcut key Ctrl+Alt+F9 forces a recalculation of *everything* in all open workbooks whether or not Excel *thinks* recalculations are needed. Changing a format does not trigger cell recalculation, so you will have to force this when you want the values to change. The use of Volatile would also work but would probably have a severe impact on your use of Excel. the VBA equivalent of the shortcut is Application.CalculateFull ' in Excel 2000 |

## Sorting on Interior Cell Color

This is a somewhat frequent request, that is going to be prone to errors in interpretation of what color is. You can obtain ColorIndex or RGB but how would you sort that meaningfully. Finally you are going to have problems with recalculaton.

This topic is covered further on a separate page: Color, Sorting on Color

## Related to Interior Color, using Count, SUM, etc.

    For Each cell In Selection  'Check for Black interior color
        If cell.Interior.ColorIndex = 1 Then
            [action]
        End If
    Next

- Count cells with a particular background cell color - PhilCxn
- ShowColor(cellref) - Patrick Molloy

If the colors you want to test are due to Conditional Formatting then use the same kind of test that you used for Conditional Formatting, and the results will be immediate (no recalculation needed) i.e.
=COUNTIF(D12:D16,TRUE)
=SUMIF(D12:D16,TRUE,E12:E16)

If the colors are not from C.F. you will have to use a User Defined Function to find this information and since formatting is not registered as a cell change you will have to wait for a recalculation to occur to get a valid answer. You can but should not make the macro Volatile, since by doing that you could bring your Excel to an extremely slow state. Examples follow in the next paragraph.

Some more Examples: You can obtain the functions used in the following examples from Chip Pearson's "Functions For Cell Colors"

> The following examples obtain the colorindex from another cell which is best, because colorindex colors can be changed by changing the pallette.

> Interior, colorindex of
> =cellcolorindex(A$3,0)

> Font, colorindex of
> =cellcolorindex(A$3,1)

> Count the cells with same interior color as A$3
> =countbycolor(A$1:A$17,cellcolorindex(A$3,0))

> Count the cells with same font color as A$3
> =countbycolor(A$1:A$16,cellcolorindex(A$3,1),1)

> Sum of the cells with same interior color as A$3
> =sumbycolor(A$1:A$16,cellcolorindex(A$3))

> #NAME? error will occur if you misspell one of the UDF (User Defined Function) above or did not install the function. A #VALUE! error may occur if you did not install a function used inside or misuse it. Instructions to install macros and User Defined Functions can be found on my formula.htm page.

> To work with shading instead of colorindex use .pattern instead of .colorindex and rename functions accordinging. Specific patterns include such name as: xlgray8, xlgrid, xlvertical

## Determining the Row color based on cell value in that row

> Conditional Formatting introduced in Excel 97 is limited to 3 conditions. With more than 3 conditions a macro would be required, such as shown below. Another kind of macro that you could use is an Event Macro.

>     Sub  ColorRowBasedOnCellValue()
>       'David McRitchie, 2001-01-17 programming -- Color row based on value
>       Application.ScreenUpdating = False
>       Application.Calculation = xlCalculationManual
>       Dim cell As Range
>       For Each cell In Intersect(Selection, ActiveCell.EntireColumn, _
>              ActiveSheet.UsedRange
>         Select Case cell.Value
>             Case Is >= 50
>                 cell.EntireRow.Interior.colorindex = 20
>             Case Is >= 40
>                 cell.EntireRow.Interior.colorindex = 37
>             Case Is >= 20
>                 cell.EntireRow.Interior.colorindex = 38
>             Case Is >= 0
>                 cell.EntireRow.Interior.colorindex = 36
>             Case Else
>                 cell.EntireRow.Interior.colorindex = 44
>         End Select
>       Next cell
>       Application.Calculation = xlCalculationAutomatic
>       Application.ScreenUpdating = False
>     End Sub

## Delete Rows Base on RED interior color in Column A

> The following will delete the entire row if it sees RED as define by ColorIndex = 3 There are some caveats
> - Conditional Formatting colors are invisible to VBA
> - The ColorIndex = 3 is the default someone could change it
> - Not everybody is going to be able to distinguish RED from color close to it and then there is Red/Green colorblindness.
> - Not all monitors are going to show colors alike, though RED is pretty safe in that regard.

>   Comments for the following code can be found below the macro DelCellsUp on another web page.

>       Sub DeleteRowsRedInColA()
>         'David McRitchie  2002-01-17
>         '  http://www.mvps.org/dmcritchie/excel/colors.htm
>         'Will not find color due to Conditional Formatting
>         Application.ScreenUpdating = False
>         Application.Calculation = xlCalculationManual   'pre XL97 xlManual
>         Dim rng As Range, ix As Long
>         Set rng = Intersect(Range("A:A"), ActiveSheet.UsedRange)
>         For ix = rng.Count To 1 Step -1
>             If rng.Item(ix).Interior.ColorIndex = 3 Then
>               rng.Item(ix).EntireRow.Delete
>             End If
>         Next
>         Application.Calculation = xlCalculationAutomatic
>         Application.ScreenUpdating = True
>       End Sub

## HEX Conversions for RGB values

Hex characters are actually characters, but represent binary numbers.

RGB values are represented by 6 hex digits. The first pair of digits represents Red, the next Green, and the last Blue. The values range from 0 to 255, or in hex from 00 to FF. Given a six hex digit representation in hex characters such as 00C0C8 as hex characters simply use left, and mid to separate them the digit pairs. Look in HELP for more information about HEX2DEC and DEC2HEX. Suppose B14 had a Long (Binary) integer in it and you want 6 hex digits for RGB. HEX2DEC and DEC2HEX are part of the Statistical Analysis Toolpak [menu] [list]

There are 256³ RGB colors (16,777,216) and only 56 colorindex colors in the palette; so a one to one match of each is not only impossible, but the colors in the palette can be reassigned to different colors.

Conversion of Font color in Excel to a hex string for HTML (via VBA code)

The following code was used in XL2HTMLx conversion of an Excel sheet to HTML. Note Excel appears to store binary values in reverse order or perhaps this is just "big-endian" (main frames, 1234 order) vs. "little-endian" (most PCs, 4321 order).

       xColor = Right("000000" & Hex(Selection.Cells(r, C).FONT.Color), 6)
       xColor = "#" & Right(xColor, 2) & Mid(xColor, 3, 2) & Left(xColor, 2)
       If xColor <> "#000000" Then _
          x = "<font color=""" & xColor & """>" & x & "</font>"

Conversion of a single binary decimal number to decimal RGB components (WS formulas)

          =RIGHT("000000" & DEC2HEX(B14),6)

If you have a character value such as 00C0C8 or you start from a Long (Binary) integer, your can incorporate the above formula into the following

          =HEX2DEC(Left(B15,2))
          =HEX2DEC(MID(B15,3,2))
          =HEX2DEC(MID(B15,5,2))

## Changing the Colors of your Excel Color Pallette

To change a color in your pallette go to Tools --> Options --> Color where you can change a color by double-clicking on a color cell. Use Reset to revert back to defaults. Also see Help --> colors, changing

## Changing the Color of your Excel Cell Comment

A frequent question in the newsgroups is how to change the default colors in the Cell Comments. The name of the author is picked up from your Tools, Options, General, User Name. (you cannot change the color of the red triangle)

Cell Comments are Tool Tips so to change your default you must change your Windows default. To change only once cell comment dclick on the border of the cell comment and make your changes.

To change Tool Tips.
Windows START, settings, control panel, Display (monitor icon)

Before continuing it might be a good idea to name your current settings and then name your new settings.

- Press [Save As] button then assign the scheme to something like Windows out of the box mmm dd, yyyyy (current date).
- Press [Save As] button then assign your own name to the scheme i.e. "David Jun 22, 2000", and all future changes would be made to this scheme.
- In the item: pull down, rather than pulling down you can simply place cursor in the box and then use the cursor to cycle up or down through the choices. Select "Tool Tip" and make changes to font, fontsize, text color, background color as wanted. not sure if you actually will change the fontname or not.

You can select parts of the windows shown which will change the item selected, but tool tips is not one of them, you have to use the pull down.

My own settings show: red text, yellow background, 8 point, MS Sans Serif, non bold, non italic. (I'm not sure what they were originally).

Also see entire comment box turns black.

More material on Cell Comments

## Changing the Colors of Worksheet Tabs

The color of the tabs is controlled by Windows, you can change but it will affect everything in Windows.

In Excel 2002 you can color the worksheet tabs. Here is a tip from Jessica Kovalik in exceltip at Microsoft's Office site.

- Select the sheets you want to color by holding down the CTRL key and clicking the tabs.
- On the Format menu, point to Sheet, and then click Tab Color. You can also right-click the sheet tab and then click Tab Color.
- Click the color you want, and click OK.

I believe the normal reason to color tabs is to provide an organization to them. You can sort sheet tabs with a macro you can enhance your sorted arrangement by preceding the sheet tab with some less conspicuous small letters. i.e. k.FunctKeys, k.ShortCutKeys You can sort your worksheets, look for "sort sheet tabs" in http://www.mvps.org/dmcritchie/excel/buildtoc.htm The main topic on that page is to create to Table of Contents with hyperlinks to the other sheets. A builtin alternative is to RClick on a scrolling arrow in lower left corner to see a list of sheets and navigate to the sheet.

## Manually Changing the Interior Color of Worksheet Cells

Setting the interior color of the active cell, specifically Applies to all cells in a selection that you can add to with the use of the Ctrl key.
Format --> cells --> patterns and colors

You can install a button on your toolbar to hasten the process, it looks like a dripping paint can.
View --> customize --> toolbars --> custom --> format
select the dripping paint bucked, marked Fill Color and drag it to your toolbar (if not already there).

## Changing the Colors of your DOS session

Color change is available at least in WinNT.

Changing colors of your DOS window may or may not work for you. I changed mine mainly in order to work with a specific package so that the wording is black on white. This is easy to change but where there is no text the color will remain black. (directions). Color can also be changed in the DOS window with the color command (Color F0), which can be put into your Autoexec.bat -- to be effective you must reboot. The screen can still turn black upon exiting an application but can be instantly reverted to white by typing Color.

## The DOS assignments of the 16 colors (0-15)

The normal VGA assignments do not apply to Excel, but they do apply to older VGA monitors used on IBM mainframes and DOS color assignments.

0 | 1 | 2 | 3 | 4 | 5 | 6 | 7
000000 Black | 000080 Navy | 008000 Green | 008080 Teal | 800000 Maroon | 800080 Purple | 808000 Olive | C0C0C0 Silver
The assignments 0-15 are NOT those used by Excel ColorIndex
8 | 9 | 10 | 11 | 12 | 13 | 14 | 15
808080 Gray | 0000FF Blue | 00FF00 Lime | 00FFFF Cyan* | FF0000 Red | FF00FF Magenta* | FFFF00 Yellow | FFFFFF White

The above colors are supported in the HTML 3.2 standard but have not been universally accepted by all browsers. In HTML the colors are Fuchsia: #FF00FF instead of Magenta; and Aqua: #00FFFF instead of Cyan.

## Color Charts on the Web

Refer to RGB Hex Triplet Color Chart for Douglas R. Jacobson's charts. [http://www.flash.net/~drj2142/rgb.html] and what was formerly [http://www.phoenix.net/~jacobson/rgb.html] is now [ http://home.flash.net/~drj2142/pages/rgbdec.html]

Hex Color Chart for the HTML Resource Guide for a faster loading variation of Jacobson's hex chart by Jack Wilson. [http://www.mta.ca/comment/wwwcon/hexchart.html] It's faster because it uses tables with BGCOLOR instead of a lot of bit maps.

Color Chart at Charles Barash's Excel site has a slider to change the background color so you can check combinations of forground and background colors in HTML. Pretty neat. Also note at the bottom of his page there is a similar chart for characters. with forground and black font.

## Color Luminance

In color television luminance is calculated (approximately) like this (1 = white, represented by 1V signal voltage):
L = R*0.3+G*0.59+B*0.11

This formula was created to make the color video signal compatible with black and white monitors/receivers. Monochrome monitors are still in use as professional viewfinders on many TV cameras, since they create a sharper image than one made from a color matrix. Posted by Harald Staff, plus the reference below.

Poynton's Color FAQ, Charles Poynton

## Colors used in other programs (not Excel)

- Syd Allan: HTML Tag an Color Test Page, has a list of other sites with color information, and has color tables of colors used in SAS.
- Color Cube Show what different background colors (in HTML) would look like from a choice of 216 (18x12) colors.

## Newsgroup Postings on Colors

RGB Colors posted collection of links by Tom Ogilvy (2000-05-10). -- and it doesn't even have the varied names of colors recognized by some of the browsers.

## Other Pages on Colors in Excel

Chip Pearson's "Functions For Cell Colors". Nice functions but no examples there, see my examples above.

## Other Pages Making Use of Color

- Worksheet Change Events, shows changing color of cells upon Change.
- Color, Sorting on Color, not recommended, at least not in any Excel through Excel 2000, but the topic does come up.

## Printers, Printing and colors -- How Printers work

- How Printers Work [The PC Technology Guide], referenced in a LockerGnome newsletter 2001-08-08]
- InkJet printers and other types, glossary of color printer terminology

## Microsoft Knowledge DataBase

Q157202 - XL97: Color Palette Looks Different in MS Excel 97
http://support.microsoft.com/support/kb/articles/Q157/2/02.asp
Q211533 - XL2000: Color Palette Looks Different in Microsoft Excel 2000
http://support.microsoft.com/support/kb/articles/q211/5/33.asp
Q291293 - XL2002: Color Palette Looks Different in Microsoft Excel 2002
http://support.microsoft.com/support/kb/articles/q291/2/93.asp

Sample Visual Basic Code to Create Color Index Table
http://support.microsoft.com/support/kb/articles/Q149/1/70.asp

Q97600 Printed Colors Different than on Screen: Blue is Purple, etc. This is not a problem with your printer driver, with Microsoft Windows, or with your printer. RGB colors (light) are additive. CMYK colors (pigments) are subtractive. Color-Matching Blues [PC Magazine Apr 9, 1996].

Q170781 XL: RGB Function May Map to Incorrect Color
The color property accepts an RGB triple and maps it to the nearest color index. When the property retrieves the color value, it returns the RGB color of the index, which may be different from the value you typed. In the example, RGB(65,0,0) is mapped to Dark Red (RGB(128,0,0)), but RGB(64,0,0) is mapped to Black (RGB(0,0,0)).

XL2002: How to Change the Color Palette for Workbooks

This page was introduced on July 12, 1998.

[My Excel Pages -- home] [INDEX to my site and the off-site pages I reference]
[Site Search -- Excel] [Go Back] [Return to TOP]

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com