Whilst you can use conditional formatting and cell fill etc. to give you almost any colour in excel there is a "special" set of 56 colours which Excel uses in custom formatting and other places. These are referred to by numbers 1-56. It is interesting and a little confusing that Excel will also allow the first 8 of these to be referenced by name, (Black, White, Red, Green, Blue, Yellow, Magenta and Cyan). This is why you can see colour names in number formats such as £#,##0.00;[Red]-£#,##0.00 which shows negative finance numbers are red.
The complete list of all 56 colors (note the US version of colours) is shown below.
This list was generated using the code below. To use this simply open the VBA editor window (<ALT><F11>) and add a module to your workbook and paste in the code. You will need to save the file as a macro-enabled workbook.
Sub display_colours()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim i As Long
Dim str0 As String, str As String
Dim x As Integer
On Error GoTo Finalise
'titles
Cells(1, 1).Value = "Color"
Cells(1, 2).Value = "HEX"
Cells(1, 3).Value = "Red"
Cells(1, 4).Value = "Green"
Cells(1, 5).Value = "Blue"
Cells(1, 6).Value = "RGB"
Cells(1, 1).EntireRow.Font.Bold = True
For i = 1 To 56
Cells(i + 1, 1).Interior.ColorIndex = i
Cells(i + 1, 1).Value = "[Color " & i & "]"
Select Case Cells(i + 1, 1).Interior.ColorIndex
Case 1, 3, 5, 9, 10, 11, 12, 13, 14, 16, 18, 21, 23, 25, 29, 30, _
31, 32, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56
Cells(i + 1, 1).Font.ColorIndex = 2
Case Else
Cells(i + 1, 1).Font.ColorIndex = 1
End Select
str0 = Right("000000" & Hex(Cells(i + 1, 1).Interior.Color), 6)
Cells(i + 1, 2) = "#" & str0
Cells(i + 1, 3).Formula = "=Hex2dec(""" & Right(str0, 2) & """)"
Cells(i + 1, 4).Formula = "=Hex2dec(""" & Mid(str0, 3, 2) & """)"
Cells(i + 1, 5).Formula = "=Hex2dec(""" & Left(str0, 2) & """)"
Cells(i + 1, 6).Value = "RGB(" & Cells(i + 1, 3) & "," & Cells(i + 1, 4) & "," & Cells(i + 1, 5) & ")"
Next i
'autofit the columns
For x = 1 To ActiveSheet.UsedRange.Columns.Count
Columns(x).EntireColumn.AutoFit
Next x
Finalise:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Comentarios