I am new to excel and have created a list but now have decided to add a fill
color to the options in the list. Is this possible so that when you pick a
certain option it wil auto fill the cell with a specific color? Thanks in
advance.

Is your list simply a list of, say, names, in a range, say a1:a10? Is it a
list using data validation. Perhaps not if you are new to Excel. No matter.
It is possible but you will need to be more specific about what you have and
what your aim is.
Your answer will be conditional formatting but as I say, it depends on what
you want to achieve.
Basically you will select c/f and give the instruction for a1 to fill red
when a1="Russell"
If you can't do it yourself with this - then give more info please and you
will get the answer.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"shaggy" wrote:

I am new to excel and have created a list but now have decided to add a fill
color to the options in the list. Is this possible so that when you pick a
certain option it wil auto fill the cell with a specific color? Thanks in
advance.

Allright that worked and it was pretty easy now that I looked at it. But it
only allowed 3 conditions. is there a way to add more? I will need 6 at this
time. I found another thread that had one but the link was broken. It was
called CFPlus. Thanks for the help!

"Russell Dawson" wrote:

Is your list simply a list of, say, names, in a range, say a1:a10? Is it a
list using data validation. Perhaps not if you are new to Excel. No matter.
It is possible but you will need to be more specific about what you have and
what your aim is.
Your answer will be conditional formatting but as I say, it depends on what
you want to achieve.
Basically you will select c/f and give the instruction for a1 to fill red
when a1="Russell"
If you can't do it yourself with this - then give more info please and you
will get the answer.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"shaggy" wrote:

I am new to excel and have created a list but now have decided to add a fill
color to the options in the list. Is this possible so that when you pick a
certain option it wil auto fill the cell with a specific color? Thanks in
advance.

Ok I have a list that has words for elements and I want to condition the
fill color when a certain wordset is used. I thought I had it working with
the following code and using numbers instead of words, but now it sees to not
be working. I really don't want it to work with numbers as I wanted the words
in there.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A3:J11")) Is Nothing Then
Select Case Target
Case 1
icolor = 41
Case 2
icolor = 44
Case 3
icolor = 15
Case 4
icolor = 3
Case 5
icolor = 2
Case 6
icolor = 35
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub


"shaggy" wrote:

Allright that worked and it was pretty easy now that I looked at it. But it
only allowed 3 conditions. is there a way to add more? I will need 6 at this
time. I found another thread that had one but the link was broken. It was
called CFPlus. Thanks for the help!

"Russell Dawson" wrote:

Is your list simply a list of, say, names, in a range, say a1:a10? Is it a
list using data validation. Perhaps not if you are new to Excel. No matter.
It is possible but you will need to be more specific about what you have and
what your aim is.
Your answer will be conditional formatting but as I say, it depends on what
you want to achieve.
Basically you will select c/f and give the instruction for a1 to fill red
when a1="Russell"
If you can't do it yourself with this - then give more info please and you
will get the answer.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"shaggy" wrote:

I am new to excel and have created a list but now have decided to add a fill
color to the options in the list. Is this possible so that when you pick a
certain option it wil auto fill the cell with a specific color? Thanks in
advance.

How about:

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Dim iColor As Long

If Target.Cells.Count 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(Target, Me.Range("A3:J11")) Is Nothing Then
'do nothing
Else
Select Case Target
Case Is = "word1", "word2", "word3"
iColor = 41
Case Is = "qwer1", "qwer2", "qwer3"
iColor = 44
Case 3
iColor = 15
Case 4
iColor = 3
Case 5
iColor = 2
Case 6
iColor = 35
Case Else
iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor
End If
End Sub

The "Option Compare Text" line tells excel not to worry about upper/lower case
differences -- but just for the code in this module.

shaggy wrote:

Ok I have a list that has words for elements and I want to condition the
fill color when a certain wordset is used. I thought I had it working with
the following code and using numbers instead of words, but now it sees to not
be working. I really don't want it to work with numbers as I wanted the words
in there.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A3:J11")) Is Nothing Then
Select Case Target
Case 1
icolor = 41
Case 2
icolor = 44
Case 3
icolor = 15
Case 4
icolor = 3
Case 5
icolor = 2
Case 6
icolor = 35
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

"shaggy" wrote:

Allright that worked and it was pretty easy now that I looked at it. But it
only allowed 3 conditions. is there a way to add more? I will need 6 at this
time. I found another thread that had one but the link was broken. It was
called CFPlus. Thanks for the help!

"Russell Dawson" wrote:

Is your list simply a list of, say, names, in a range, say a1:a10? Is it a
list using data validation. Perhaps not if you are new to Excel. No matter.
It is possible but you will need to be more specific about what you have and
what your aim is.
Your answer will be conditional formatting but as I say, it depends on what
you want to achieve.
Basically you will select c/f and give the instruction for a1 to fill red
when a1="Russell"
If you can't do it yourself with this - then give more info please and you
will get the answer.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"shaggy" wrote:

I am new to excel and have created a list but now have decided to add a fill
color to the options in the list. Is this possible so that when you pick a
certain option it wil auto fill the cell with a specific color? Thanks in
advance.

--

Dave Peterson

OK I added the case is = and the Option Compare Text and got it to work.
Thanks a lot! Now another thing I have a problem with is the worksheet has a
two color fill if empty and if I use the list and it fills the cell it leaves
the color in it even if I delete the word out of the list. Then if I clear
content it goes white. Could I somehow set the color as a variable and reset
it if the word is gone? Not sure how to do this either. Thanks in advance. I
didn't click the yes on did it answer the question (even though you did)
because I wasn't sure if you would look back if you saw that it was answered.
Thanks again!!!!

"Dave Peterson" wrote:

How about:

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Dim iColor As Long

If Target.Cells.Count 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(Target, Me.Range("A3:J11")) Is Nothing Then
'do nothing
Else
Select Case Target
Case Is = "word1", "word2", "word3"
iColor = 41
Case Is = "qwer1", "qwer2", "qwer3"
iColor = 44
Case 3
iColor = 15
Case 4
iColor = 3
Case 5
iColor = 2
Case 6
iColor = 35
Case Else
iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor
End If
End Sub

The "Option Compare Text" line tells excel not to worry about upper/lower case
differences -- but just for the code in this module.

shaggy wrote:

Ok I have a list that has words for elements and I want to condition the
fill color when a certain wordset is used. I thought I had it working with
the following code and using numbers instead of words, but now it sees to not
be working. I really don't want it to work with numbers as I wanted the words
in there.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A3:J11")) Is Nothing Then
Select Case Target
Case 1
icolor = 41
Case 2
icolor = 44
Case 3
icolor = 15
Case 4
icolor = 3
Case 5
icolor = 2
Case 6
icolor = 35
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

"shaggy" wrote:

Allright that worked and it was pretty easy now that I looked at it. But it
only allowed 3 conditions. is there a way to add more? I will need 6 at this
time. I found another thread that had one but the link was broken. It was
called CFPlus. Thanks for the help!

"Russell Dawson" wrote:

Is your list simply a list of, say, names, in a range, say a1:a10? Is it a
list using data validation. Perhaps not if you are new to Excel. No matter.
It is possible but you will need to be more specific about what you have and
what your aim is.
Your answer will be conditional formatting but as I say, it depends on what
you want to achieve.
Basically you will select c/f and give the instruction for a1 to fill red
when a1="Russell"
If you can't do it yourself with this - then give more info please and you
will get the answer.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"shaggy" wrote:

I am new to excel and have created a list but now have decided to add a fill
color to the options in the list. Is this possible so that when you pick a
certain option it wil auto fill the cell with a specific color? Thanks in
advance.

--

Dave Peterson
.

Oh and what does this do?

Dim iColor As Long

I saw this was different from mine as mine said integer which what I thought
meant that the color would be chosen by the integer that signified it. Thanks.

"Dave Peterson" wrote:

How about:

Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Dim iColor As Long

If Target.Cells.Count 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(Target, Me.Range("A3:J11")) Is Nothing Then
'do nothing
Else
Select Case Target
Case Is = "word1", "word2", "word3"
iColor = 41
Case Is = "qwer1", "qwer2", "qwer3"
iColor = 44
Case 3
iColor = 15
Case 4
iColor = 3
Case 5
iColor = 2
Case 6
iColor = 35
Case Else
iColor = xlNone
End Select
Target.Interior.ColorIndex = iColor
End If
End Sub

The "Option Compare Text" line tells excel not to worry about upper/lower case
differences -- but just for the code in this module.

shaggy wrote:

Ok I have a list that has words for elements and I want to condition the
fill color when a certain wordset is used. I thought I had it working with
the following code and using numbers instead of words, but now it sees to not
be working. I really don't want it to work with numbers as I wanted the words
in there.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A3:J11")) Is Nothing Then
Select Case Target
Case 1
icolor = 41
Case 2
icolor = 44
Case 3
icolor = 15
Case 4
icolor = 3
Case 5
icolor = 2
Case 6
icolor = 35
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub

"shaggy" wrote:

Allright that worked and it was pretty easy now that I looked at it. But it
only allowed 3 conditions. is there a way to add more? I will need 6 at this
time. I found another thread that had one but the link was broken. It was
called CFPlus. Thanks for the help!

"Russell Dawson" wrote:

Is your list simply a list of, say, names, in a range, say a1:a10? Is it a
list using data validation. Perhaps not if you are new to Excel. No matter.
It is possible but you will need to be more specific about what you have and
what your aim is.
Your answer will be conditional formatting but as I say, it depends on what
you want to achieve.
Basically you will select c/f and give the instruction for a1 to fill red
when a1="Russell"
If you can't do it yourself with this - then give more info please and you
will get the answer.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.


"shaggy" wrote:

I am new to excel and have created a list but now have decided to add a fill
color to the options in the list. Is this possible so that when you pick a
certain option it wil auto fill the cell with a specific color? Thanks in
advance.

--

Dave Peterson
.