Solved: Color Names

[Philcjr]

Is there a way to display the color's name if you have the color's Index number?

Here is my code:


Sub ColorCells()
Dim Color_Index As Integer
For Color_Index = 1 To 56
Range("A" & Color_Index).Value = "ColorIndex # " & Color_Index
Range("B" & Color_Index).Interior.ColorIndex = Color_Index
'Range("C" & Color_Index). Would like the name here
Next Color_Index

End Sub

[Bob Phillips]

Is there a way to display the color's name if you have the color's Index number?

Here is my code:


Sub ColorCells()
Dim Color_Index As Integer
For Color_Index = 1 To 56
Range("A" & Color_Index).Value = "ColorIndex # " & Color_Index
Range("B" & Color_Index).Interior.ColorIndex = Color_Index
'Range("C" & Color_Index). Would like the name here
Next Color_Index

End Sub


The colour index is just an offset into the colour palette, and so doesn't represent a colour, they canj be modified. So, basically, no. But here is a routine I posted on OzGrid that works out what the current fill colour name is


Function GetColourIndexName()
Dim sText As String
Dim iPos As Long
Dim iColorIndex As Long

sText = CommandBars("Formatting").Controls("&Fill Color").TooltipText
iPos = InStr(sText, "(")
If iPos > 0 Then
sText = Mid(sText, iPos + 1, InStr(sText, ")") - iPos - 1)
GetColourIndexName = sText
End If
End Function

[Philcjr]

Bob,

Thanks for your reply to my post. However, I am still a bit lost. I have studied your "Function" and understand how it works. Like you pointed out, it will only return the color name (tooltiptext) for the color that has been selected on the fill palette.

Is there a way to look at a cell and have the cell's color/tooltiptext returned in the cell next to it?

Phil

[mvidas]

In all honesty, I don't know if you'll be able to get a name for each.
You can change a color that is associated with the color index using RGB, something like
ActiveWorkbook.Colors(21)=RGB(213,71,139)And unless you want to assign names to the 16,777,216 difference color options, you may be stuck trying to get a name for some of these.

What you can do is return the red/green/blue values (0 to 255) for each number, if that helps?Sub ColorCells()
Dim Color_Index As Integer, RGBValue() As Long
For Color_Index = 1 To 56
Range("A" & Color_Index).Value = "ColorIndex # " & Color_Index
Range("B" & Color_Index).Interior.ColorIndex = Color_Index
RGBValue = FindRGB(Range("B" & Color_Index).Interior.Color)
Range("C" & Color_Index).Value = RGBValue(0) & "," & RGBValue(1) & "," & RGBValue(2)
Next 'Color_Index
End Sub
Function FindRGB(ByVal TheColor As Long) As Long()
Dim TempArr() As Long
ReDim TempArr(2) As Long
TempArr(2) = Int(TheColor / 65536)
TempArr(1) = Int((TheColor - TempArr(2) * 65536) / 256)
TempArr(0) = TheColor - TempArr(1) * 256 - TempArr(2) * 65536
FindRGB = TempArr
End Functionor even change the end of the sub to RGBValue = FindRGB(Range("B" & Color_Index).Interior.Color)
Range("C" & Color_Index).Value = RGBValue(0)
Range("D" & Color_Index).Value = RGBValue(1)
Range("E" & Color_Index).Value = RGBValue(2)
Just an idea at least
Matt

[Philcjr]

Matt,

Thanks, very cool. Although this is just something I am playing with, and is not urgent, I was hoping that there was a way to capture the color's name.

Thanks to all for their input, and if anyone else has any comments, please do add them.

Phil