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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Wed, 16 Sep 2009 15:58:31 -0400, "Rick Rothstein"
wrote:

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