Color Index
 

Please put the following words into VB Code:

msgbox "The background color is " & whatever the background color is o
the selected cell.
i've got how to give the number corresponding to the color, but i woul
like to display the actual word. for instance in stead of "Th
background color is 1" i want it to say "The background color i
white"
Thanks

--
Message posted from http://www.ExcelForum.com

Color Index
 

AFAIK this can't be done.

--

Vasant

"ForSale " wrote in message
...
Please put the following words into VB Code:

msgbox "The background color is " & whatever the background color is of
the selected cell.
i've got how to give the number corresponding to the color, but i would
like to display the actual word. for instance in stead of "The
background color is 1" i want it to say "The background color is
white"
Thanks,


---
Message posted from http://www.ExcelForum.com/

Color Index
 

ForSale wrote:
*Please put the following words into VB Code:

msgbox "The background color is " & whatever the background color i
of the selected cell.
i've got how to give the number corresponding to the color, but
would like to display the actual word. for instance in stead of "Th
background color is 1" i want it to say "The background color i
white"
Thanks, *

I think you need an UDF to do that. Here is a sample UDF in my site.

http://puremis.net/excel/code/072.shtml

Please try like this.


Code
-------------------

Option Base 1

Sub TestingUDF()
MsgBox "Selected color is " & AnalyzeColor(ActiveCell)
End Sub

Function AnalyzeColor(Target As Range, Optional sType As String = "text")
Dim aIdx As Variant
Dim aClr As Variant
Dim ret As Variant
aIdx = Array(1, 53, 52, 51, 49, 11, 55, 56, 9, 46, 12, _
10, 14, 5, 47, 16, 3, 45, 43, 50, 42, 41, _
13, 48, 7, 44, 6, 4, 8, 33, 54, 15, 38, 40, _
36, 35, 34, 37, 39, 2)
aClr = Array("Black", "Brown", "Olive Green", "Dark Green", "Dark Teal", _
"Dark Blue", "Indigo", "Gray-80%", "Dark Red", "Orange", "Dark Yellow", _
"Green", "Teal", "Blue", "Blue-Gray", "Gray-50%", "Red", "Light Orange", _
"Lime", "Sea Green", "Aqua", "Light Blue", "Violet", "Gray-40%", "Pink", _
"Gold", "Yellow", "Bright Green", "Turqoise", "Sky Blue", "Plum", _
"Gray-25%", "Rose", "Tan", "Light Yellow", "Light Green", "Light Turqoise", _
"Pale Blue", "Lavendar", "White")

ret = Application.Match(Target.Interior.ColorIndex, aIdx, 0)
sType = LCase(sType)
Select Case sType
Case "text"
AnalyzeColor = IIf(IsError(ret), "Custom Color or No Color", aClr(ret))
Case "index"
AnalyzeColor = IIf(IsError(ret), CLng(xlNone), aIdx(ret))
Case "rgb"
AnalyzeColor = IIf(IsError(ret), GetRGB(xlNone), GetRGB(CLng(aIdx(ret))))
End Select
End Function


Function SumColor(ColorRange As Range, Target As Range)
Dim c As Range
Dim rColor As Range
For Each c In Target
If c.Interior.ColorIndex = ColorRange.Interior.ColorIndex Then
If rColor Is Nothing Then
Set rColor = c
Else
Set rColor = Union(rColor, c)
End If
End If
Next
If rColor Is Nothing Then
SumColor = 0
Else
SumColor = Application.WorksheetFunction.Sum(rColor)
End If
End Function


Function GetRGB(lColor As Long) As Variant
Dim r As Long
Dim g As Long
Dim b As Long
r = lColor Mod 256
g = Int(lColor / 256) Mod 256
b = Int(lColor / 256 / 256)
GetRGB = "#" & Right("0" & Hex(r), 2) & _
Right("0" & Hex(g), 2) & _
Right("0" & Hex(b), 2)
End Function

-------------------


--
Message posted from http://www.ExcelForum.com

Color Index
 

Problem with this routine is that it returns the standard colour. If the
user changes the colour, it returns the wrong colour name.

As the names are nor exposed, there is no reliable method.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Colo " wrote in message
...
ForSale wrote:
*Please put the following words into VB Code:

msgbox "The background color is " & whatever the background color is
of the selected cell.
i've got how to give the number corresponding to the color, but i
would like to display the actual word. for instance in stead of "The
background color is 1" i want it to say "The background color is
white"
Thanks, *

I think you need an UDF to do that. Here is a sample UDF in my site.

http://puremis.net/excel/code/072.shtml

Please try like this.


Code:
--------------------

Option Base 1

Sub TestingUDF()
MsgBox "Selected color is " & AnalyzeColor(ActiveCell)
End Sub

Function AnalyzeColor(Target As Range, Optional sType As String =
"text")
Dim aIdx As Variant
Dim aClr As Variant
Dim ret As Variant
aIdx = Array(1, 53, 52, 51, 49, 11, 55, 56, 9, 46, 12, _
10, 14, 5, 47, 16, 3, 45, 43, 50, 42, 41, _
13, 48, 7, 44, 6, 4, 8, 33, 54, 15, 38, 40, _
36, 35, 34, 37, 39, 2)
aClr = Array("Black", "Brown", "Olive Green", "Dark Green", "Dark Teal",
_
"Dark Blue", "Indigo", "Gray-80%", "Dark Red", "Orange", "Dark Yellow",
_
"Green", "Teal", "Blue", "Blue-Gray", "Gray-50%", "Red", "Light Orange",
_
"Lime", "Sea Green", "Aqua", "Light Blue", "Violet", "Gray-40%", "Pink",
_
"Gold", "Yellow", "Bright Green", "Turqoise", "Sky Blue", "Plum", _
"Gray-25%", "Rose", "Tan", "Light Yellow", "Light Green", "Light
Turqoise", _
"Pale Blue", "Lavendar", "White")

ret = Application.Match(Target.Interior.ColorIndex, aIdx, 0)
sType = LCase(sType)
Select Case sType
Case "text"
AnalyzeColor = IIf(IsError(ret), "Custom Color or No Color", aClr(ret))
Case "index"
AnalyzeColor = IIf(IsError(ret), CLng(xlNone), aIdx(ret))
Case "rgb"
AnalyzeColor = IIf(IsError(ret), GetRGB(xlNone),
GetRGB(CLng(aIdx(ret))))
End Select
End Function


Function SumColor(ColorRange As Range, Target As Range)
Dim c As Range
Dim rColor As Range
For Each c In Target
If c.Interior.ColorIndex = ColorRange.Interior.ColorIndex Then
If rColor Is Nothing Then
Set rColor = c
Else
Set rColor = Union(rColor, c)
End If
End If
Next
If rColor Is Nothing Then
SumColor = 0
Else
SumColor = Application.WorksheetFunction.Sum(rColor)
End If
End Function


Function GetRGB(lColor As Long) As Variant
Dim r As Long
Dim g As Long
Dim b As Long
r = lColor Mod 256
g = Int(lColor / 256) Mod 256
b = Int(lColor / 256 / 256)
GetRGB = "#" & Right("0" & Hex(r), 2) & _
Right("0" & Hex(g), 2) & _
Right("0" & Hex(b), 2)
End Function

--------------------



---
Message posted from http://www.ExcelForum.com/

Color Index
 

Bob, Many thanks for your suggestion.

-----Original Message-----
Problem with this routine is that it returns the standard
colour. If the
user changes the colour, it returns the wrong colour name.

As the names are nor exposed, there is no reliable method.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Colo " wrote in
message
...
ForSale wrote:
*Please put the following words into VB Code:

msgbox "The background color is " & whatever the
background color is
of the selected cell.
i've got how to give the number corresponding to the
color, but i
would like to display the actual word. for instance
in stead of "The
background color is 1" i want it to say "The
background color is
white"
Thanks, *

I think you need an UDF to do that. Here is a sample
UDF in my site.

http://puremis.net/excel/code/072.shtml

Please try like this.


Code:
--------------------

Option Base 1

Sub TestingUDF()
MsgBox "Selected color is " & AnalyzeColor(ActiveCell)
End Sub

Function AnalyzeColor(Target As Range, Optional sType
As String =
"text")
Dim aIdx As Variant
Dim aClr As Variant
Dim ret As Variant
aIdx = Array(1, 53, 52, 51, 49, 11, 55, 56, 9, 46,
12, _
10, 14, 5, 47, 16, 3, 45, 43, 50, 42, 41, _
13, 48, 7, 44, 6, 4, 8, 33, 54, 15, 38, 40, _
36, 35, 34, 37, 39, 2)
aClr = Array("Black", "Brown", "Olive Green", "Dark
Green", "Dark Teal",
_
"Dark Blue", "Indigo", "Gray-80%", "Dark
Red", "Orange", "Dark Yellow",
_
"Green", "Teal", "Blue", "Blue-Gray", "Gray-
50%", "Red", "Light Orange",
_
"Lime", "Sea Green", "Aqua", "Light
Blue", "Violet", "Gray-40%", "Pink",
_
"Gold", "Yellow", "Bright Green", "Turqoise", "Sky
Blue", "Plum", _
"Gray-25%", "Rose", "Tan", "Light Yellow", "Light
Green", "Light
Turqoise", _
"Pale Blue", "Lavendar", "White")

ret = Application.Match(Target.Interior.ColorIndex,
aIdx, 0)
sType = LCase(sType)
Select Case sType
Case "text"
AnalyzeColor = IIf(IsError(ret), "Custom Color or No
Color", aClr(ret))
Case "index"
AnalyzeColor = IIf(IsError(ret), CLng(xlNone), aIdx
(ret))
Case "rgb"
AnalyzeColor = IIf(IsError(ret), GetRGB(xlNone),
GetRGB(CLng(aIdx(ret))))
End Select
End Function


Function SumColor(ColorRange As Range, Target As
Range)
Dim c As Range
Dim rColor As Range
For Each c In Target
If c.Interior.ColorIndex =
ColorRange.Interior.ColorIndex Then
If rColor Is Nothing Then
Set rColor = c
Else
Set rColor = Union(rColor, c)
End If
End If
Next
If rColor Is Nothing Then
SumColor = 0
Else
SumColor = Application.WorksheetFunction.Sum(rColor)
End If
End Function


Function GetRGB(lColor As Long) As Variant
Dim r As Long
Dim g As Long
Dim b As Long
r = lColor Mod 256
g = Int(lColor / 256) Mod 256
b = Int(lColor / 256 / 256)
GetRGB = "#" & Right("0" & Hex(r), 2) & _
Right("0" & Hex(g), 2) & _
Right("0" & Hex(b), 2)
End Function

--------------------



---
Message posted from http://www.ExcelForum.com/



.