Hi. This is just an idea...

...etc
Case "Christlike"
iColor = 2
Case "Absent"
iColor = 35
Case Else
'Even 36, Odd 43
iColor = 36 + 7 * (Target.Row Mod 2)
End Select
Target.Interior.ColorIndex = iColor

= = =
Dana DeLouis



On 3/11/2010 11:06 PM, shaggy wrote:
YeeHaw!!! That did it. I figured there was a way to do it. I just don't know
the syntax for this VB code. That is slick. I had a hard time finding out the
color id #s on the row colors as they were called color scheme. But I found a
code list here
http://www.ozgrid.com/VBA/ReturnCellColor.htm. I didn't think they were here
until I just followed the list until I hit them and they were just named
different. The internet is awesome as it gets noobs like me in touch with
helpful experts like you. Thanks a lot. Here is the finished code

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:J12")) Is Nothing Then
'do nothing
Else
Select Case Target
Case Is = "Effort"
iColor = 41
Case Is = "Sportsmanship"
iColor = 44
Case Is = "Offense"
iColor = 15
Case Is = "Defense"
iColor = 3
Case Is = "Christlike"
iColor = 2
Case Is = "Absent"
iColor = 35
Case Else
iColor = -99999
End Select
If iColor = -99999 Then
If Target.Row Mod 2 = 0 Then
Target.Interior.ColorIndex = 36 'even color row
Else
Target.Interior.ColorIndex = 43 'odd color row
End If
Else
Target.Interior.ColorIndex = iColor
End If
End If
End Sub





"Dave Peterson" wrote:

Change this portion:

if icolor = -99999 then
if target.row mod 2 = 0 then
Target.Interior.ColorIndex = #### 'even color row
else
Target.Interior.ColorIndex = #### 'odd color row
end if
else
Target.Interior.ColorIndex = iColor
end if

shaggy wrote:

OK that works but it leaves whatever color was in it last in the cell. So if
I had a case in it and it was filled with that color it leaves that color
when I clear it. The rows are a certain color every other row as in lt green
and then off white. Odd rows being the lt green and the evens being off
white. Is there a way to look at the row number and replace the color based
on the row? I know you have gone over and above helping me on this and I do
appreciate it so if this is getting into in depth coding I will understand if
this is too much to ask. Thanks for your help as you really have helped. I
will be looking into this further and trying new things as needed.

"Dave Peterson" wrote:

I don't use the web interface, so I don't know how that works.

Change the declaration of iColor to long.

shaggy wrote:

I tried the code but it errorred out on the:

Case Else
iColor = -99999 ' this was highlighted yellow
I keep selecting notify me and it doesn't send it to my email even though I
checked and made sure my email was correct. Thanks and sorry it took so long
to get back to you.

"Dave Peterson" wrote:

Your original code used:
Target.Interior.ColorIndex = icolor
no matter if it had been changed in the preceding lines. And if it had not been
changed, then it was the default value for that type of variable (0 for
integer/longs).

I just used xlnone since it seemed to document the code better.

Maybe...

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 = -99999
End Select
if icolor = -99999 then
'do nothing, don't touch it
else
Target.Interior.ColorIndex = iColor
end if
End If
End Sub

shaggy wrote:

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
.


--

Dave Peterson
.


--

Dave Peterson
.


--

Dave Peterson
.



--
= = = = = = =
HTH :)
Dana DeLouis