#### help with problem formula

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

--
Message posted from http://www.ExcelForum.com

```
 0
6/23/2004 3:38:51 PM
excel 39879 articles. 2 followers.

12 Replies
748 Views

Similar Articles

[PageSpeed] 35

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

--
Don Guillett
SalesAid Software
donaldb@281.com
> this formula worked up to yesterday. i had a power surg and now it only
> 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 all
> agan.   (it only copies b= 38)
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
Don
6/23/2004 5:28:52 PM
```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

"Don Guillett" <donaldb@281.com> wrote in message
news:OCH6NeUWEHA.3200@TK2MSFTNGP09.phx.gbl...
> 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
>
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> > this formula worked up to yesterday. i had a power surg and now it only
> > 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 all
> > agan.   (it only copies b= 38)
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >
>
>

```
 0
delouis (422)
6/24/2004 12:59:02 AM
```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

--
Message posted from http://www.ExcelForum.com

```
 0
6/24/2004 2:10:14 PM
```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

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

<Original...>
> 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

```
 0
delouis (422)
6/24/2004 4:19:05 PM
```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.???

--
Message posted from http://www.ExcelForum.com

```
 0
6/28/2004 3:19:23 AM
```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.

>
> this formula worked up to yesterday. i had a power surg and now it only
> 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 all
> 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 or
> do I just go to the top and excape with h itting the x.????
>
> ---
> Message posted from http://www.ExcelForum.com/

--

Dave Peterson
ec35720@msn.com
```
 0
ec35720 (10082)
6/28/2004 9:52:40 PM
```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

--
Message posted from http://www.ExcelForum.com

```
 0
6/29/2004 2:37:16 PM
```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

--
Message posted from http://www.ExcelForum.com

```
 0
6/29/2004 2:45:17 PM
```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

--
Don Guillett
SalesAid Software
donaldb@281.com
> 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 all
> 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 posting
> this all the time  is not working  would it be out of line to ask one
> 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
>
> joe
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
Don
6/29/2004 3:10:46 PM
```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

> 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 all
> 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 posting
> this all the time  is not working  would it be out of line to ask one
> 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
>
> joe
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
delouis (422)
6/29/2004 3:17:04 PM
```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

> 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 all
> 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 posting
> this all the time  is not working  would it be out of line to ask one
> 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
>
> joe
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
delouis (422)
6/29/2004 3:17:04 PM
```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

> 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 all
> 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 posting
> this all the time  is not working  would it be out of line to ask one
> 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
>
> joe
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

```
 0
delouis (422)
6/29/2004 3:26:37 PM

Similar Artilces:

How do you combine two columns to one? Help!!!!!!
I have address numbers in column A and street names in column B, I want to combine all of both columns into one without going through the whole process for each individual cells. HELP!!!!!! "sttrumpet" <sttrumpet@discussions.microsoft.com> wrote in message news:F72E43CE-CA99-4CD0-BCA1-85EFADCB459D@microsoft.com... > I have address numbers in column A and street names in column B, I want to > combine all of both columns into one without going through the whole process > for each individual cells. HELP!!!!!! Use the fuction =A1&B1 in cell B1 Drag the function ...

VB Code help
Here is a portion of one of my VB Macros in Excel: If Selection.Count > 0 Then MsgBox ("Average = ") & (RunningTotal / Selection.Count), vbInformation, "Average" End If My question: How do I format the number (RunningTotal/Selection.Count) to look like 0.000, instead of 0.0000000000000000?? What should my code look like with the new formatting. Thanks Chris Chris, Try this: ....Format((RunningTotal / Selection.Count), "0.000")... -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- &qu...

Help with formula #18
Hi All: I am WAY over my head here, and you all have always been great help, so hopefully someone can help with this. I had someone write this for me to use as an index at the top of a spread sheet. It works beautifully . The problem is I would like to move this index to sheet 2 of the same spreadsheet and when I copy and paste it, it does'nt work. Is there anyone who can modify this to work as an index in sheet 2 and will search sheet 1 for the results? TIA Keith =IF(#REF!="","",IF(ISNA(MATCH(#REF!,\$H\$31:\$H\$65536,0)),"",HYPERLINK("#"&C...

Help needed with Money 2003 files
Hi there, I've been using money 2003 for years even when upgrading to new PC's as I had the original disk. I've just had to rebuild my hard drive and now cannot find the disk anywhere so cannot install Money. As you cannot now buy Money, I've tried downloading the trial version of Money so that I can access all the account details/balances but it says that they're not compatible with earlier versions of money. Is there anything that I can do to get these files open or have I lost all my records for the last 6 years? I'd really appreciate your assistance. Jayne EggHead...

Non-editable CRichEditCtrl... caret problem
Hello, I have a Rich Edit Control that I want to use to just view RTF, no editing, no selecting, nothing. Everything works so far except that the caret will appear if I scroll the window, but goes away as soon as I do anything else. Basically it's a minor annoyance cause it goes away, but the point is I still saw it. This is the code I use... am I missing something in order to make sure that caret never appears? Or did I do something wrong already? The problem seems to come from the SetFocus method call, but without it I can't scroll with the mouse wheel. Thanks, Andrew // CMyRi...

Outlook 2007 Mobile Services Problem
Hi, We have developed and set up an Outlook 2007 Mobile Service layer and able to send and receive Short Message Service messages (SMS's) through it. But there is a problem in our situation, when a user sets up an mobile account on his or her Outlook, a mobile address book is automatically generated containing the contact items from the users contact book those having mobile numbers, but there is no option/way for Exchange Global Address (GAL), when the user tries to send an SMS Message by selecting recipients from GAL, instead of Outlook Mobile Address Book. Outlook tries to send it as an...

Cached Exchange and GAL Problem
I have a windows domain with an Exchange organisation that houses multiple e-mail domains. Each E-mail domain belongs to a different company and has its own GAL. When the users login via terminal services and start Outlook they only see the users within their own company. This is fine, no problem here......Some of these users connect with the same username and password using Outlook 2003 RPC over HTTP that connects to exactly the smae Exchange organisation. When using cached exchange mode and the user clicks on the address book, it displays the entire organisation including information about t...

Need help with using a bar graph in a report
I have a report that separates information about representatives by manager. I need the graphs to give a quick summary on a few stats from each rep but only show for that manager's team and not all information. For example: Manager A has RepA, RepB, and RepC under him. Each rep has Aux1, Aux2, Aux3 information which needs to be displayed. My goal is to show Manager A with his Reps A,B, and C of their Aux1, 2, and 3. Then, under the next manager Header I want Manager B to show Rep D, E, and F with their Aux1, 2, and 3 information. Etc thru Manager G. Please help... Th...

Formula problem #9
Hi, I have entered a small formula in a cell =A1+B1 the result is also fine but if i have edit the formula i have to again type the entire thing. Is there a shortcut to this where i can easily do the editing of the formula, i guess i am missing something out here. Hi, I guess you are not able to see the formula in the formula bar if it is so then go to Tools|Options|View|check the formula bar check box|ok -- _______________________ Click "Yes" if it helps ________ Thanks Suleman Peerzade "Niroo" wrote: > Hi, > > I have entered a small formula in a cell ...

Subform Data Entry Problems
I have a subform based on a query linked to the Form by "ContactID". The query works fine when run independent of the subform. I am able to enter all data fields and where necessary the autonumber function assigns properly. But when I try to use the subform all records related by my table "tblADDRESS" do not allow data entry (the fields are not locked). I imagine the ContactID Master/Child link in some way is interfering with the query in Form view, but I've tried every variation of join properties within the Query for the Subform and in the Query for the main...

Help with Userform
I need to setup a restricted means of allowing users to edit data in existing cells on a spreadsheet through a macro (userform?). I want to create a userform that will read data from these existing cells and display their contents in a series of text boxes. The user will then have the choice to edit any data item, if they need to, or leave the data as is. Upon the user hitting the OK key on the userform, the edited data will be entered in their previous cell locations. Any help or examples will be appreciated! Thanks ...

I have Outlook 2002 as Part of Office XP. I have also installed Service Pack 3. Recently (before and now after the installation of SP3) when I try to open a URL link in an e-mail I get the response that the Link Browser can't be found and it opens the My Documents Folder. Any idea how to fix this?? Thanks. Browse to the location of explorer.exe (or your system default browser) and double click it. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal accoun...

[Excel 2003] problem in files with pivottables after install Office Service Pack 3
Hi all. System: Windows XP Pro SP2, Office 2003 Professional + Service Pack 3 I have a problem with some xls files after install office service pack 3. With service pack 2 this files normal open. With service pack 3 after open file displays dialog (my translate from russian) "In workbook ... have contents which can not be read. Try restore contents of workbook ? If you trust source of this workbook, press button YES". If I press "No" - file not opens. If I press "Yes", displays next dialog. This dialog form content a list of corrections in file. Biggest pa...

OMA Problem Getting Desperate & Running out of Ideas
Hi I am looking to roll out OMA through my corp, but can't seem to solve this prob I am having with logging on I have been through the available papers and there none out there which apply to this or work for it I enabled OMA on my local Exchange 2003 box which is running Server 2000, the OMA directory in IIS contains the correct mailboxes. However when I connect through I.E6 to http://servername:port/oma, I seem to sucessfully logon, but then get then get blocked with a HTTP 500 INTERNAL SERVER ERROR page cannot be displayed screen. I also get this when I browse OMA through IIS. Though I...

ARGGG! OWA Problem
We are using Outlook Web Access with Exchange server 5.5 (on a Windows 2000 computer). For some strange reason, I cannot successfully connect anymore. I get either of the following errors. It's either 1) Failed to get inbox Or 2) Failed to connect to the Microsoft Exchange server, <server name> No other users seem to be affected, just me! I have checked permissions through the IIS management console, and I have even re-applied service pack 4 for Exchange 5.5. I can log on to the network and get my mail through Outlook on the desktop with no problem. I'm stumped! Any...

Release version problems
Hi, I built a release version of my application, and an installer. The application runs fine on my two XP development machines. When I installed it on a windows 2000 machine (SP4) I got the following error when I tried to display a bitmap in my app: "The instruction at "0x00000001" referenced memory at "0x00000001". The memory could not be "read". Then I installed it on another XP machine, and the application crashed and closed itself when performing a certain operation (could also be bitmap related, but not necessarily). I am using the paintlib library in...

Running problem
VWD 2010 I copy all my asp project into a physical location in my pc. Set up a virtual directory under IIS. Open the browser for the main page. eg http://localhost/TestSite20100707/main.aspx Got the error on the page showing the webconfig contents: Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: Unrecognized attribute 'targetFramework'. Note that attribute name...

On behalf of problem
No one seems to be able to figure this one out. All my incoming emails are now coming in 'on behalf of' the actual sender. The subject line in there but all of the body of the email is blank. These are all emails senders who regularly send me email and I have done nothing to my Outlook. No one else on my network has this problem but me and it just appeared out of no where. What is the issue here and how can I fix it? Stu. manchu <sjwarkentin@fastmail.fm> wrote: > No one seems to be able to figure this one out. All my incoming > emails are now coming in 'on beh...

Exchange 2000 OWA Problem
I can access my Exchange 2000 server via OWA from within the network fine. However when I try and access from outside the network, I can't and receive a "page can't be displayed" error message. I believe my DNS settings are correct, but when I go to http://servername/exchange (using fully qualified domain server name), the error message comes up. Any help would be appreciated. Thanks. Martin On Sat, 27 Aug 2005 16:59:01 -0700, Martin <Martin@discussions.microsoft.com> wrote: >I can access my Exchange 2000 server via OWA from within the network fine. >Ho...

Hello, Our new warehouse manager entered, received and committed a PO from the wrong vendor - over 100 items! Does anyone know a way to either reverse the PO or change the vendor? Please help!!!! Thank you! diana Diana, You can reverse the PO by opening it again in receiving mode. Use the Quick Scan button and add an item to the PO (any item). This will 'unlock' or re-open the PO. You can now delete the item you just added back off. Next you would enter the quantity 'Received to Date' of each item into the 'Quantity Received' column as a negative. Fo...