help with problem formula

[jladika]

this formula worked up to yesterday. i had a power surg and now it onl
give me one color.

Private Sub LCase(Target.Val Target As Range)
Dim myColor As Long
If Target.Cells.Count > 1 Then Exit Sub
If Intersect ( Target, Me.Range("d:d")) Is Nothing Then Exit Sub

Select Case LCase(Target.Value)
Case Is = "a" : myColor = 33
Case Is = "b" : myColor = 38
Case Is = "c" : myColor = 20
Case Is = "e" : myColor = 35
Case Is = "f" : myColor = 40
Case Is = "g" : myColor = 8
Case Else
myColor =x1None
End selection
Target.Interior.ColorIndex = myColor

End Sub

please show me why it only makes it one color? I need it to do al
agan. (it only copies b= 38

 

[Don Guillett]

I think you want this to be automatic and you had several other problems.
This works.
right click sheet tab>view code>insert this>save

Now this will work in col D

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
Select Case LCase(Target)
Case "a": myColor = 33
Case "b": myColor = 38
Case "c": myColor = 20
Case "e": myColor = 35
Case "f": myColor = 40
Case "g": myColor = 8
Case Else
myColor = 0
End Select
Target.Interior.ColorIndex = myColor
end sub

 

[Dana DeLouis]

If one wanted, the Case's could be combined. One idea...

t = LCase(t)
Select Case t
Case "a", "b", "c", "e", "f", "g"
myColor = 27932390 Mod (Asc(t) - 56)
Case Else
myColor = xlNone
End Select
'...etc.

Dana DeLouis

I think you want this to be automatic and you had several other problems.
This works.
right click sheet tab>view code>insert this>save

Now this will work in col D

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
Select Case LCase(Target)
Case "a": myColor = 33
Case "b": myColor = 38
Case "c": myColor = 20
Case "e": myColor = 35
Case "f": myColor = 40
Case "g": myColor = 8
Case Else
myColor = 0
End Select
Target.Interior.ColorIndex = myColor
end sub

 

[jladika]

will this new way
give me my color selections if so
where is it i can put the colors i need to match each cell

a= 38
b= 30
d=8
e=44
thanks

jo

 

[Dana DeLouis]

Hi. It was just a part of Don's excellent post. You would just use his
"Target.Interior.ColorIndex = myColor" at the end. However, my little
change won't work since you changed your Colors. In your original post, you
had a=33, b=38, etc. and there was no "d." Better to use his technique.

Dana

 

[jladika]

this formula worked up to yesterday. i had a power surg and now it onl
give me one color.

Private Sub LCase(Target.Val Target As Range)
Dim myColor As Long
If Target.Cells.Count > 1 Then Exit Sub
If Intersect ( Target, Me.Range("d:d")) Is Nothing Then Exit Sub

Select Case LCase(Target.Value)
Case Is = "a" : myColor = 33
Case Is = "b" : myColor = 38
Case Is = "c" : myColor = 20
Case Is = "e" : myColor = 35
Case Is = "f" : myColor = 40
Case Is = "g" : myColor = 8
Case Else
myColor =x1None
End selection
Target.Interior.ColorIndex = myColor

End Sub

please show me why it only makes it one color? I need it to do al
agan. (it only copies b= 38)

sorry I dont think i stated what i really need
why does this not work now??? i have deleted all the about and
tryed to put it back again and I cant get it to work at all.
tell me once i put it in is there something i have to do to save it o
do I just go to the top and excape with h itting the x.???

 

[Dave Peterson]

You changed the name of the procedure.

In Don's original post, it was:

Private Sub Worksheet_Change(ByVal Target As Range)
not: Private Sub LCase(Target.Val Target As Range)

Also, you changed some other stuff:

myColor = xlNone
You now have: x1(one)None

End Select
you now have: End Selection

===
Sometimes, it's just better to go back to the original post and modify that
again.

 

[jladika]

I am sure i bet this topic into the ground

but it is very inportant to me to get this completed
I was very happy when i got it to work and i have tryed to do what al
of you have said and still cant get it to work
I know it not the way to help other learn online but i think me postin
this all the time is not working would it be out of line to ask on
of you to call me direct
941-755-5257 and walk me through this ongoing problem
this would be a great help to me thanks

jo

 

[jladika]

would like to thank all who tryed to help me with my problem
this is very important to me to get this wight and it seem so simpl
you all of you
please would it be out of line to ask one of you to call me and walk m
through is please 941-755-5257 office please ask for JO

 

[Don Guillett]

Can't you just look at my original posting and use that?
Here it is again. BTW, many of us on the list are for hire.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
Select Case LCase(Target)
Case "a": myColor = 33
Case "b": myColor = 38
Case "c": myColor = 20
Case "e": myColor = 35
Case "f": myColor = 40
Case "g": myColor = 8
Case Else
myColor = 0
End Select
Target.Interior.ColorIndex = myColor
end sub

 

[Dana DeLouis]

Not sure, but usually code that looks like this...

Private Sub Worksheet_Change(ByVal Target As Range)

usually goes on the code window for the specific Worksheet, and not on a
regular Module sheet. Could this be the problem? In the vba editor, double
click the name of the worksheet in the "Project" window, and try pasting the
code there.

I'm just guessing here because you said this worked earlier...

Private Sub LCase(Target.Val Target As Range)

The use of the name "Target" appears to me that this was once used on a
Sheet module, or perhaps the "ThisWorkbook" module.

Also note that your function name "LCase" is also the name of a built-in vba
function. It is best not to use the same name as a built-in Function.

Again, not sure, but something to look into.

HTH
Dana DeLouis

 

[Dana DeLouis]

Not sure, but usually code that looks like this...

Private Sub Worksheet_Change(ByVal Target As Range)

usually goes on the code window for the specific Worksheet, and not on a
regular Module sheet. Could this be the problem? In the vba editor, double
click the name of the worksheet in the "Project" window, and try pasting the
code there.

I'm just guessing here because you said this worked earlier...

Private Sub LCase(Target.Val Target As Range)

The use of the name "Target" appears to me that this was once used on a
Sheet module, or perhaps the "ThisWorkbook" module.

Also note that your function name "LCase" is also the name of a built-in vba
function. It is best not to use the same name as a built-in Function.

Again, not sure, but something to look into.

HTH
Dana DeLouis

 

[Dana DeLouis]

Not sure, but usually code that looks like this...

Private Sub Worksheet_Change(ByVal Target As Range)

usually goes on the code window for the specific Worksheet, and not on a
regular Module sheet. Could this be the problem? In the vba editor, double
click the name of the worksheet in the "Project" window, and try pasting the
code there.

I'm just guessing here because you said this worked earlier...

Private Sub LCase(Target.Val Target As Range)

The use of the name "Target" appears to me that this was once used on a
Sheet module, or perhaps the "ThisWorkbook" module.

Also note that your function name "LCase" is also the name of a built-in vba
function. It is best not to use the same name as a built-in Function.

Also note your use of "Case is "a": etc
Usually, "is" is used as ... Case Is > 100: etc.

Perhaps use it as Don mentioned...
Case "a": etc.

Again, not sure, but something to look into. Feel free to drop a private
email to me if you still have problems.

HTH
Dana DeLouis