Color Index

[ForSale]

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

 

[Vasant Nanavati]

AFAIK this can't be done.

 

[Colo]

*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

 

[Bob Phillips]

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)

 

[Guest]

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)

As String =
"text") Green", "Dark Teal",
_ Red", "Orange", "Dark Yellow",
_ 50%", "Red", "Light Orange",
_

Blue", "Violet", "Gray-40%", "Pink",