When ising cell validating with a list, I want the color of the cell to also
change per each item on the list assigned to the cell. Also, I want to
incorporate the data into the cell itself so that I do not have source data.
How do I do that?

You can use something like below in the code module of the applicable
worksheet to change the interior color of the cell after a selection
is made. I do not believe you can separate the list contents from a
source though. Others may know of a way to accomplish this, but I do
not know of one.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$4" Then
Select Case Target.Text
Case "fred"
Target.Interior.ColorIndex = 36
Case "tom"
Target.Interior.ColorIndex = 40
Case Else
Target.Interior.ColorIndex = xlNone
End Select
End If
End Sub
Christopher Leigh Stout wrote:
When ising cell validating with a list, I want the color of the cell to also
change per each item on the list assigned to the cell. Also, I want to
incorporate the data into the cell itself so that I do not have source data.
How do I do that?

You can type the items directly into the source dialog box.

Comma delimited like fred,tom,gord


Gord Dibben MS Excel MVP

On Wed, 03 Oct 2007 05:16:37 -0700, JW wrote:

You can use something like below in the code module of the applicable
worksheet to change the interior color of the cell after a selection
is made. I do not believe you can separate the list contents from a
source though. Others may know of a way to accomplish this, but I do
not know of one.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$4" Then
Select Case Target.Text
Case "fred"
Target.Interior.ColorIndex = 36
Case "tom"
Target.Interior.ColorIndex = 40
Case Else
Target.Interior.ColorIndex = xlNone
End Select
End If
End Sub
Christopher Leigh Stout wrote:
When ising cell validating with a list, I want the color of the cell to also
change per each item on the list assigned to the cell. Also, I want to
incorporate the data into the cell itself so that I do not have source data.
How do I do that?

I have applied the data to the equation. But I want each item on the list to
have its own cell color.

"JW" wrote:

You can use something like below in the code module of the applicable
worksheet to change the interior color of the cell after a selection
is made. I do not believe you can separate the list contents from a
source though. Others may know of a way to accomplish this, but I do
not know of one.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$4" Then
Select Case Target.Text
Case "fred"
Target.Interior.ColorIndex = 36
Case "tom"
Target.Interior.ColorIndex = 40
Case Else
Target.Interior.ColorIndex = xlNone
End Select
End If
End Sub
Christopher Leigh Stout wrote:
When ising cell validating with a list, I want the color of the cell to also
change per each item on the list assigned to the cell. Also, I want to
incorporate the data into the cell itself so that I do not have source data.
How do I do that?