Cell Shading Colour Property

[Tim Childs]

Hi

The first function gives a cell's shading colour-index property and the
second converts it to a description. Has anyone the list of indexes and a
colour description so I no longer need the Else statement near the end. I
wnated to cover the basic colours in the Format cells tab, 40+ I suppose

Thanks

Tim

PS hope the US colleagues will forgive "colour" variant spelling

Function ShowColourIndexNo(Cell As Object) As Integer
ShowColourIndexNo = Cell.Interior.ColorIndex
End Function

Function ConvertColorIndexToText(Cell As Object) As String
Dim Temp As Variant

Select Case Cell.Value
Case -4142
Temp = "No colour"
Case 3
Temp = "Bright red"
Case 4
Temp = "Bright green"
Case 5
Temp = "Dark blue"
Case 34
Temp = "Light blue"
Case 35
Temp = "Light green"
Case Else
Temp = "Non-specified"
End Select

ConvertColorIndexToText = Temp

End Function

 

[Rick Rothstein]

I don't think there are "official" names for all the colors, but if you
copy/paste this code line into the Immediate window and execute it (you can
also but a Sub/EndSub around it and execute it as a macro if you want), the
code will fill the first 56 rows with the 56 available color indexes... you
can look at them and decide on the names to describe them yourself.

For X = 1 To 56: Cells(X, "A").Interior.ColorIndex = X: Next

 

[Rick Rothstein]

I meant to point out that the colors assigned to the various color indexes
are not "fixed"... they can be changed. For example, if you executed this
line...

ActiveWorkbook.Colors(3)=vbGreen

the Red color normally associated with ColorIndex 3 would change to green.
You can set it back to its red color by executing this line...

ActiveWorkbook.Colors(3)=vbRed

The point is, the colors a user sees for a given ColorIndex might be
different than the color you see; so, naming the colors as you are doing
might possibly lead to misinterpretations.

 

[Gord Dibben]

Not official as Rick states but these are close.

=CellColor(A1,true)

Note: if colors have been modified from default...............all goes out
the window<g>

Function CellColor(rCell As Range, Optional ColorName As Boolean)
Dim strColor As String, iIndexNum As Integer
Application.Volatile
Select Case rCell.Interior.ColorIndex
Case 1
strColor = "Black"
iIndexNum = 1
Case 53
strColor = "Brown"
iIndexNum = 53
Case 52
strColor = "Olive Green"
iIndexNum = 52
Case 51
strColor = "Dark Green"
iIndexNum = 51
Case 49
strColor = "Dark Teal"
iIndexNum = 49
Case 11
strColor = "Dark Blue"
iIndexNum = 11
Case 55
strColor = "Indigo"
iIndexNum = 55
Case 56
strColor = "Gray-80%"
iIndexNum = 56
Case 9
strColor = "Dark Red"
iIndexNum = 9
Case 46
strColor = "Orange"
iIndexNum = 46
Case 12
strColor = "Dark Yellow"
iIndexNum = 12
Case 10
strColor = "Green"
iIndexNum = 10
Case 14
strColor = "Teal"
iIndexNum = 14
Case 5
strColor = "Blue"
iIndexNum = 5
Case 47
strColor = "Blue-Gray"
iIndexNum = 47
Case 16
strColor = "Gray-50%"
iIndexNum = 16
Case 3
strColor = "Red"
iIndexNum = 3
Case 45
strColor = "Light Orange"
iIndexNum = 45
Case 43
strColor = "Lime"
iIndexNum = 43
Case 50
strColor = "Sea Green"
iIndexNum = 50
Case 42
strColor = "Aqua"
iIndexNum = 42
Case 41
strColor = "Light Blue"
iIndexNum = 41
Case 13
strColor = "Violet"
iIndexNum = 13
Case 48
strColor = "Gray-40%"
iIndexNum = 48
Case 7
strColor = "Pink"
iIndexNum = 7
Case 44
strColor = "Gold"
iIndexNum = 44
Case 6
strColor = "Yellow"
iIndexNum = 6
Case 4
strColor = "Bright Green"
iIndexNum = 4
Case 8
strColor = "Turqoise"
iIndexNum = 8
Case 33
strColor = "Sky Blue"
iIndexNum = 33
Case 54
strColor = "Plum"
iIndexNum = 54
Case 15
strColor = "Gray-25%"
iIndexNum = 15
Case 38
strColor = "Rose"
iIndexNum = 38
Case 40
strColor = "Tan"
iIndexNum = 40
Case 36
strColor = "Light Yellow"
iIndexNum = 36
Case 35
strColor = "Light Green"
iIndexNum = 35
Case 34
strColor = "Light Turquoise"
iIndexNum = 34
Case 37
strColor = "Pale Blue"
iIndexNum = 37
Case 39
strColor = "Lavendar"
iIndexNum = 39
Case 2
strColor = "White"
iIndexNum = 2
Case 37
strColor = "Pale Blue"
iIndexNum = 37
Case 17
strColor = "Periwinkle"
iIndexNum = 17
Case 18
strColor = "Plum"
iIndexNum = 18
Case 19
strColor = "Ivory"
iIndexNum = 19
Case 20
strColor = "Light Turquoise"
iIndexNum = 20
Case 21
strColor = "Dark Purple"
iIndexNum = 21
Case 22
strColor = "Coral"
iIndexNum = 22
Case 23
strColor = "Ocean Blue"
iIndexNum = 23
Case 24
strColor = "Ice Blue"
iIndexNum = 24
Case 25
strColor = "Dark Blue"
iIndexNum = 23
Case 26
strColor = "Pink"
iIndexNum = 26
Case 27
strColor = "Yellow"
iIndexNum = 27
Case 28
strColor = "Turquoise"
iIndexNum = 28
Case 29
strColor = "Violet"
iIndexNum = 29
Case 30
strColor = "Dark Red"
iIndexNum = 30
Case 31
strColor = "Teal"
iIndexNum = 31
Case 32
strColor = "Blue"
iIndexNum = 32
End Select

If ColorName = True Then
CellColor = strColor

Else
CellColor = iIndexNum
End If

End Function


Gord Dibben MS Excel MVP

 

[EricG]

Here's a manual way to get all the color names in the default color pallet
for Excel 2003:

Draw a rectangle on a sheet and double-click it to bring up the format dialog.
Click on the Fill color drop-down so that all the fill colors show up.
Hover the pointer over each color swatch until the color name comes up.

Now all you have to do is associate each color swatch with its color index,
which is an exercise that I will leave to the reader. Well, maybe not - you
can get the color index of each swatch by recording a macro in which you draw
that rectangle, and then set its color to each of the fill colors in the
pallet. You will then have a list of the color index values.

HTH,

Eric

 

[Tim Childs]

Hi Gord

that's just the ticket! thanks v much.

pl can you just explain the volatile at the beginning: I did not have that
in my basic function and I do get some problems (e.g. get the #NAME erro
initially and have to force recalculation) when copying the function to
other workbooks (after moving the module into the other book, of course)

finally, why do plain cells have an index of -4142 not a small positive
integer?

thanks

Tim

 

[Tim Childs]

Hi Eric

Thanks but I am stuck in the Excel dark ages - Excel 2000. Is that vintage,
like an old wine?

bw

Tim

 

[Gord Dibben]

Since changing the color in a cell does not trigger calculation the
"volatile" is superfluous.

The originator of the code used it and I have not bothered to remove.


Gord Dibben MS Excel MVP

 

[Tim Childs]

Hi

thanks for that - I was hoping the volatile would sort out the issue of the
#NAME error..

bw

Tim