Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex values,
so, for any selection, I can msgbox the name of the fillcolor e.g. Red, Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but they're
not on the toolbar palette, and I want to know if Excel has names for them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Pete,

No you cannot do that easily, as the names are not stored anywhere (AFAIK).
A few colours have VB constant values, such as vbRed, vbBlue, and so on, but
these are very few. You could pick up the colorindex value and translate
that, but anyone can change a colour.

If you want to go the translation route, here is a starter for you


Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray50 As Long = 16
Private Const xlCIPlum As Long = 18
Private Const xlCILightTurquoise As Long = 20
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCIPaleBlue As Long = 37
Private Const xlCIRose As Long = 38
Private Const xlCILavender As Long = 39
Private Const xlCITan As Long = 40
Private Const xlCILightBlue As Long = 41
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46
Private Const xlCIBlueGray As Long = 47
Private Const xlCIGray40 As Long = 48
Private Const xlCIDarkTeal As Long = 49
Private Const xlCISeaGreen As Long = 50
Private Const xlCIDarkGreen As Long = 51
Private Const xlCIBrown As Long = 53
Private Const xlCIIndigo As Long = 55
Private Const xlCIGray80 As Long = 56


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g. Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Bob,

Thanks for this - the code will be most useful.
The palette shows colors for values 1 to 16 and 33-56 but 17-31 (not on the
palette) also return colors and these are the ones I was puzzled by.
I hate being a completeist! :-)

Regards

Pete

"Bob Phillips" wrote:

Pete,

No you cannot do that easily, as the names are not stored anywhere (AFAIK).
A few colours have VB constant values, such as vbRed, vbBlue, and so on, but
these are very few. You could pick up the colorindex value and translate
that, but anyone can change a colour.

If you want to go the translation route, here is a starter for you


Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray50 As Long = 16
Private Const xlCIPlum As Long = 18
Private Const xlCILightTurquoise As Long = 20
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCIPaleBlue As Long = 37
Private Const xlCIRose As Long = 38
Private Const xlCILavender As Long = 39
Private Const xlCITan As Long = 40
Private Const xlCILightBlue As Long = 41
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46
Private Const xlCIBlueGray As Long = 47
Private Const xlCIGray40 As Long = 48
Private Const xlCIDarkTeal As Long = 49
Private Const xlCISeaGreen As Long = 50
Private Const xlCIDarkGreen As Long = 51
Private Const xlCIBrown As Long = 53
Private Const xlCIIndigo As Long = 55
Private Const xlCIGray80 As Long = 56


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g. Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Here is a good general reference for the colors although he hasn't added the
color names for the charts.

http://www.mvps.org/dmcritchie/excel/colors.htm

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Bob,

Thanks for this - the code will be most useful.
The palette shows colors for values 1 to 16 and 33-56 but 17-31 (not on
the
palette) also return colors and these are the ones I was puzzled by.
I hate being a completeist! :-)

Regards

Pete

"Bob Phillips" wrote:

Pete,

No you cannot do that easily, as the names are not stored anywhere
(AFAIK).
A few colours have VB constant values, such as vbRed, vbBlue, and so on,
but
these are very few. You could pick up the colorindex value and translate
that, but anyone can change a colour.

If you want to go the translation route, here is a starter for you


Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray50 As Long = 16
Private Const xlCIPlum As Long = 18
Private Const xlCILightTurquoise As Long = 20
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCIPaleBlue As Long = 37
Private Const xlCIRose As Long = 38
Private Const xlCILavender As Long = 39
Private Const xlCITan As Long = 40
Private Const xlCILightBlue As Long = 41
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46
Private Const xlCIBlueGray As Long = 47
Private Const xlCIGray40 As Long = 48
Private Const xlCIDarkTeal As Long = 49
Private Const xlCISeaGreen As Long = 50
Private Const xlCIDarkGreen As Long = 51
Private Const xlCIBrown As Long = 53
Private Const xlCIIndigo As Long = 55
Private Const xlCIGray80 As Long = 56


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g.
Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but
they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Thanks for this Tom, some useful stuff on here!

Pete



"Tom Ogilvy" wrote:

Here is a good general reference for the colors although he hasn't added the
color names for the charts.

http://www.mvps.org/dmcritchie/excel/colors.htm

--
Regards,
Tom Ogilvy


"Peter Rooney" wrote in message
...
Bob,

Thanks for this - the code will be most useful.
The palette shows colors for values 1 to 16 and 33-56 but 17-31 (not on
the
palette) also return colors and these are the ones I was puzzled by.
I hate being a completeist! :-)

Regards

Pete

"Bob Phillips" wrote:

Pete,

No you cannot do that easily, as the names are not stored anywhere
(AFAIK).
A few colours have VB constant values, such as vbRed, vbBlue, and so on,
but
these are very few. You could pick up the colorindex value and translate
that, but anyone can change a colour.

If you want to go the translation route, here is a starter for you


Private Const xlCIBlack As Long = 1
Private Const xlCIWhite As Long = 2
Private Const xlCIRed As Long = 3
Private Const xlCIBrightGreen As Long = 4
Private Const xlCIBlue As Long = 5
Private Const xlCIYellow As Long = 6
Private Const xlCIPink As Long = 7
Private Const xlCITurquoise As Long = 8
Private Const xlCIDarkRed As Long = 9
Private Const xlCIGreen As Long = 10
Private Const xlCIDarkBlue As Long = 11
Private Const xlCIDarkYellow As Long = 12
Private Const xlCIViolet As Long = 13
Private Const xlCITeal As Long = 14
Private Const xlCIGray25 As Long = 15
Private Const xlCIGray50 As Long = 16
Private Const xlCIPlum As Long = 18
Private Const xlCILightTurquoise As Long = 20
Private Const xlCISkyBlue As Long = 33
Private Const xlCILightGreen As Long = 35
Private Const xlCILightYellow As Long = 36
Private Const xlCIPaleBlue As Long = 37
Private Const xlCIRose As Long = 38
Private Const xlCILavender As Long = 39
Private Const xlCITan As Long = 40
Private Const xlCILightBlue As Long = 41
Private Const xlCIAqua As Long = 42
Private Const xlCILime As Long = 43
Private Const xlCIGold As Long = 44
Private Const xlCILightOrange As Long = 45
Private Const xlCIOrange As Long = 46
Private Const xlCIBlueGray As Long = 47
Private Const xlCIGray40 As Long = 48
Private Const xlCIDarkTeal As Long = 49
Private Const xlCISeaGreen As Long = 50
Private Const xlCIDarkGreen As Long = 51
Private Const xlCIBrown As Long = 53
Private Const xlCIIndigo As Long = 55
Private Const xlCIGray80 As Long = 56


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g.
Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but
they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Hi Pete,

The "default" chart colours are also named though 10 of these are duplicates
of other colours in a default palette.

17 # Periwinkle
18 Plum
19 # Ivory
20 # Light Turquoise
21 Dark Purple
22 # Coral
23 # Ocean Blue
24 # Ice Blue


25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue

# denotes non-duplicated colour.

There's no direct way to return these names, I do it by comparing the long
colour value in a lookup table of values & names.

Regards,
Peter T


"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g. Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

I didn't see Bob's when I posted - you could add the extra "#" colours to
Bob's list of constants. Bear in mind, as Bob indicated, colorindex's might
not relate to named colours in a default palette.

Return a list of long colour values

For i = 1 To 56
Cells(i, 1) = ThisWorkbook.Colors(i)
Next

Put names in Col B
Sort the colour values
Get rid of duplicates

Now you have a lookup table !

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Pete,

The "default" chart colours are also named though 10 of these are
duplicates
of other colours in a default palette.

17 # Periwinkle
18 Plum
19 # Ivory
20 # Light Turquoise
21 Dark Purple
22 # Coral
23 # Ocean Blue
24 # Ice Blue


25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue

# denotes non-duplicated colour.

There's no direct way to return these names, I do it by comparing the long
colour value in a lookup table of values & names.

Regards,
Peter T


"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g. Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but
they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

typo

colorindex's might
not relate to named colours in a default palette.

in a Customised palette

Peter T


"Peter T" <peter_t@discussions wrote in message
...
I didn't see Bob's when I posted - you could add the extra "#" colours to
Bob's list of constants. Bear in mind, as Bob indicated, colorindex's
might
not relate to named colours in a default palette.

Return a list of long colour values

For i = 1 To 56
Cells(i, 1) = ThisWorkbook.Colors(i)
Next

Put names in Col B
Sort the colour values
Get rid of duplicates

Now you have a lookup table !

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Pete,

The "default" chart colours are also named though 10 of these are
duplicates
of other colours in a default palette.

17 # Periwinkle
18 Plum
19 # Ivory
20 # Light Turquoise
21 Dark Purple
22 # Coral
23 # Ocean Blue
24 # Ice Blue


25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue

# denotes non-duplicated colour.

There's no direct way to return these names, I do it by comparing the
long
colour value in a lookup table of values & names.

Regards,
Peter T


"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g.
Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but
they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Peter,

Thanks again - although I'm not sure how my original thread came to be split
into two!

Pete



"Peter T" wrote:

typo

colorindex's might
not relate to named colours in a default palette.

in a Customised palette

Peter T


"Peter T" <peter_t@discussions wrote in message
...
I didn't see Bob's when I posted - you could add the extra "#" colours to
Bob's list of constants. Bear in mind, as Bob indicated, colorindex's
might
not relate to named colours in a default palette.

Return a list of long colour values

For i = 1 To 56
Cells(i, 1) = ThisWorkbook.Colors(i)
Next

Put names in Col B
Sort the colour values
Get rid of duplicates

Now you have a lookup table !

Regards,
Peter T

"Peter T" <peter_t@discussions wrote in message
...
Hi Pete,

The "default" chart colours are also named though 10 of these are
duplicates
of other colours in a default palette.

17 # Periwinkle
18 Plum
19 # Ivory
20 # Light Turquoise
21 Dark Purple
22 # Coral
23 # Ocean Blue
24 # Ice Blue


25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue

# denotes non-duplicated colour.

There's no direct way to return these names, I do it by comparing the
long
colour value in a lookup table of values & names.

Regards,
Peter T


"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g.
Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but
they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Thanks for those fill-ins Peter, will update my list.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter T" <peter_t@discussions wrote in message
...
Hi Pete,

The "default" chart colours are also named though 10 of these are
duplicates
of other colours in a default palette.

17 # Periwinkle
18 Plum
19 # Ivory
20 # Light Turquoise
21 Dark Purple
22 # Coral
23 # Ocean Blue
24 # Ice Blue


25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue

# denotes non-duplicated colour.

There's no direct way to return these names, I do it by comparing the long
colour value in a lookup table of values & names.

Regards,
Peter T


"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g. Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but
they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Hi Bob,

For the sake of completeness there was "another" typo in the original list

20 Light Turquoise is same as colorindex 34, but 21 Dark Purple is a unique
colour.

Regards,
Peter T


"Bob Phillips" wrote in message
...
Thanks for those fill-ins Peter, will update my list.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter T" <peter_t@discussions wrote in message
...
Hi Pete,

The "default" chart colours are also named though 10 of these are
duplicates
of other colours in a default palette.

17 # Periwinkle
18 Plum
19 # Ivory
20 # Light Turquoise
21 Dark Purple
22 # Coral
23 # Ocean Blue
24 # Ice Blue


25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue

# denotes non-duplicated colour.

There's no direct way to return these names, I do it by comparing the
long
colour value in a lookup table of values & names.

Regards,
Peter T


"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g.
Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but
they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Thanks Peter, but I noticed that when I updated the list.

Bob

"Peter T" <peter_t@discussions wrote in message
...
Hi Bob,

For the sake of completeness there was "another" typo in the original list

20 Light Turquoise is same as colorindex 34, but 21 Dark Purple is a
unique
colour.

Regards,
Peter T


"Bob Phillips" wrote in message
...
Thanks for those fill-ins Peter, will update my list.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter T" <peter_t@discussions wrote in message
...
Hi Pete,

The "default" chart colours are also named though 10 of these are
duplicates
of other colours in a default palette.

17 # Periwinkle
18 Plum
19 # Ivory
20 # Light Turquoise
21 Dark Purple
22 # Coral
23 # Ocean Blue
24 # Ice Blue


25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue

# denotes non-duplicated colour.

There's no direct way to return these names, I do it by comparing the
long
colour value in a lookup table of values & names.

Regards,
Peter T


"Peter Rooney" wrote in
message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g.
Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but
they're
not on the toolbar palette, and I want to know if Excel has names
for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

One more thing - Pete - I note you in a couple of places you almost
"excused" your self for asking this question. You shouldn't!

Bear in mind a very significant proportion of the population have some
degree of colour vision impairment. Where appropriate naming colours can be
very useful. Though I'm not sure how intuitive the name "Periwinkle" is!

Regards,
Peter T

"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g. Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete

Peter,
You sometimes wonder whether the things you're trying to accomplish are a
bit silly, but, as somebody usually knows the answer, then if it's something
that someone ELSE has thoght of, so perhaps not!
I just wish somebody could answer my other thread about the deleted target!
:-)

Thanks for your help

Pete

"Peter T" wrote:

One more thing - Pete - I note you in a couple of places you almost
"excused" your self for asking this question. You shouldn't!

Bear in mind a very significant proportion of the population have some
degree of colour vision impairment. Where appropriate naming colours can be
very useful. Though I'm not sure how intuitive the name "Periwinkle" is!

Regards,
Peter T

"Peter Rooney" wrote in message
...
Good morning, all!

Is there any way in VBA to trap the name of the interior.colorindex
values,
so, for any selection, I can msgbox the name of the fillcolor e.g. Red,
Blue,
Rose, Tan etc?

The reason for this is that colors 17 to 32 can also be used, but they're
not on the toolbar palette, and I want to know if Excel has names for
them.

Yes, I KNOW I need to get out more! :-)

Thanks in advance!

Pete