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