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]

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)

 

[Guest]

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