fill color in a list

[shaggy]

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.

 

[shaggy]

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!

 

[shaggy]

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

 

[Dave Peterson]

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]

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!!!!

 

[shaggy]

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]

In modern computers, there's no reason to use "as long" or "as single". The pc
will do its work using longs and doubles.

So instead of telling excei to use a single and letting it convert things to and
from longs, I'll just start my variables as long.

 

[Dave Peterson]

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]

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]

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

Change the declaration of iColor to long.

 

[shaggy]

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]

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]

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]

When I want that colorindex number, I'll start a new workbook and record a macro
when I change the fill color of a couple of cells.

Then I can look at the recorded code, steal the numbers and delete the delete
that temporary workbook.

 

[shaggy]

Thanks again Dave, I will remember that trick.

When I want that colorindex number, I'll start a new workbook and record a macro
when I change the fill color of a couple of cells.

Then I can look at the recorded code, steal the numbers and delete the delete
that temporary workbook.

 

[Dana DeLouis]

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