Color cell validation?

[jjh]

Hi:
Thanks for taking the time to read my question. I want to configure a cell in Excel for color validation and I don't know how to do it. What I want is to configure a cell with a drop-down list of colors only, so the user can select from a set of predefined colors.
I know how to create a data validation list by placing characters in a verticle list of cells, naming the grouping, then use data validation using a list to provide user selection. I also know how to conditionally format the cell based on the value chosen from the list, e.g. if 'R' is chosen from the list, the cell color changes to red, and the 'R' from the named list is placed in the cell.
What I want to do is choose from a list of colors and only place the color in the cell.
I tried using non-printing ASCII characters in the list but Excel does not accept them. I tried to color the cells that comprise the drop down list, but the colors do not show up in the drop down list, nor when a member of the list is chosen. All I want to do is have the user select from a drop down list of colors only. Can anyone suggest how to do this?
Thanks for your help
-J

[Franz Verga]

jjh wrote:
> Hi:
> Thanks for taking the time to read my question. I want to configure a
> cell in Excel for color validation and I don't know how to do it. What
> I want is to configure a cell with a drop-down list of colors only, so
> the user can select from a set of predefined colors.
> I know how to create a data validation list by placing characters in a
> verticle list of cells, naming the grouping, then use data validation
> using a list to provide user selection. I also know how to
> conditionally format the cell based on the value chosen from the list,
> e.g. if 'R' is chosen from the list, the cell color changes to red,
> and the 'R' from the named list is placed in the cell.
> What I want to do is choose from a list of colors and only place the
> color in the cell.
> I tried using non-printing ASCII characters in the list but Excel does
> not accept them. I tried to color the cells that comprise the drop
> down list, but the colors do not show up in the drop down list, nor
> when a member of the list is chosen. All I want to do is have the
> user select from a drop down list of colors only. Can anyone suggest
> how to do this?
> Thanks for your help
> -J


AFAIK there is no way to make such a "color cell validation"...

--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[Bob Phillips]

Here is a way that adds an option to the right-click menu, with a sleection
of colours to choose from

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars("Cell").Controls("Get Colour").Delete
On Error GoTo 0
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Application.CommandBars("Cell").Controls("Get Colour").Delete
On Error GoTo 0

With Application.CommandBars("Cell")
With .Controls.Add(Type:=msoControlPopup, temporary:=True)
.BeginGroup = True
.Caption = "Get Colour"
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.BeginGroup = True
.Caption = "Red"
.Parameter = "Red"
.OnAction = "GetColour"
End With
With .Controls.Add(Type:=msoControlButton, temporary:=True)
.Caption = "Blue"
.Parameter = "Blue"
.OnAction = "GetColour"
End With
'etc.
End With
End With

End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code



and in a standard code module, add

'-----------------------------*------------------------------*--------------
--
Function GetColour() As Long
'-----------------------------*------------------------------*--------------
--
With Application.CommandBars.ActionControl
Select Case .Parameter
Case "Red": ActiveCell.Interior.ColorIndex = 3
Case "Blue": ActiveCell.Interior.ColorIndex = 5
'etc.
End Select
End With
End Function



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"jjh" <[email protected]> wrote in message
news:[email protected]...
>
> Hi:
> Thanks for taking the time to read my question. I want to configure a
> cell in Excel for color validation and I don't know how to do it. What
> I want is to configure a cell with a drop-down list of colors only, so
> the user can select from a set of predefined colors.
> I know how to create a data validation list by placing characters in a
> verticle list of cells, naming the grouping, then use data validation
> using a list to provide user selection. I also know how to
> conditionally format the cell based on the value chosen from the list,
> e.g. if 'R' is chosen from the list, the cell color changes to red, and
> the 'R' from the named list is placed in the cell.
> What I want to do is choose from a list of colors and only place the
> color in the cell.
> I tried using non-printing ASCII characters in the list but Excel does
> not accept them. I tried to color the cells that comprise the drop down
> list, but the colors do not show up in the drop down list, nor when a
> member of the list is chosen. All I want to do is have the user select
> from a drop down list of colors only. Can anyone suggest how to do
> this?
> Thanks for your help
> -J
>
>
> --
> jjh
> ------------------------------------------------------------------------
> jjh's Profile:
http://www.excelforum.com/member.php...o&userid=36089
> View this thread: http://www.excelforum.com/showthread...hreadid=559608
>

[excelent]

well i no its not quite what ur looking for, but maby u can use this

http://pmexcelent.dk/PicColor.xls

[L. Howard Kittle]

Here's my shot at it. With the drop down in F1.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 6 Then Exit Sub
If Target.Row <> 1 Then Exit Sub
Target.Interior.ColorIndex = xlNone

If Range("F1").Value = "red" Then
With Target.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 3

ElseIf Range("F1").Value = "blue" Then
With Target.Interior
.ColorIndex = 41
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 41

ElseIf Range("F1").Value = "green" Then
With Target.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
Target.Font.ColorIndex = 4

End If
End Sub

HTH
Regards,
Howard

"jjh" <[email protected]> wrote in message
news:[email protected]...
>
> Hi:
> Thanks for taking the time to read my question. I want to configure a
> cell in Excel for color validation and I don't know how to do it. What
> I want is to configure a cell with a drop-down list of colors only, so
> the user can select from a set of predefined colors.
> I know how to create a data validation list by placing characters in a
> verticle list of cells, naming the grouping, then use data validation
> using a list to provide user selection. I also know how to
> conditionally format the cell based on the value chosen from the list,
> e.g. if 'R' is chosen from the list, the cell color changes to red, and
> the 'R' from the named list is placed in the cell.
> What I want to do is choose from a list of colors and only place the
> color in the cell.
> I tried using non-printing ASCII characters in the list but Excel does
> not accept them. I tried to color the cells that comprise the drop down
> list, but the colors do not show up in the drop down list, nor when a
> member of the list is chosen. All I want to do is have the user select
> from a drop down list of colors only. Can anyone suggest how to do
> this?
> Thanks for your help
> -J
>
>
> --
> jjh
> ------------------------------------------------------------------------
> jjh's Profile:
> http://www.excelforum.com/member.php...o&userid=36089
> View this thread: http://www.excelforum.com/showthread...hreadid=559608
>