help me set up a Event Macros

i am looking to use more than 3 colors in my spreadsheet
i can do the conditional formatting for 3 colors but need help 
to use 5 colors.
I am new to this so please tell me how to do this 
thank

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

0
6/8/2004 10:22:57 PM
excel 39879 articles. 2 followers. Follow

5 Replies
571 Views

Similar Articles

[PageSpeed] 47

You could use a worksheet_change event:

rightclick on the worksheet tab that should have this behavior.  Select View
code and paste this in the code window.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myColor As Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
    
    Select Case LCase(Target.Value)
        Case Is = "val 1": myColor = 34
        Case Is = "val 2": myColor = 33
        Case Is = "val 3": myColor = 32
        Case Is = "val 4": myColor = 31
        Case Else
            myColor = xlNone
    End Select
    
    Target.Interior.ColorIndex = myColor
    
End Sub

Adjust the colors to what you want.  Add as many tests as you need.  And adjust
the range (I used column A).

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And if you want to learn more about what events are:

Chip Pearson has some notes about events at:
http://www.cpearson.com/excel/events.htm

David McRitchie also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm

"jladika <" wrote:
> 
> i am looking to use more than 3 colors in my spreadsheet
> i can do the conditional formatting for 3 colors but need help
> to use 5 colors.
> I am new to this so please tell me how to do this
> thanks
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/8/2004 10:45:43 PM
can you tell me what part of of this do i copy and paste
from where to where jo

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

0
6/9/2004 1:27:26 AM
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myColor As Long

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
    
    Select Case LCase(Target.Value)
        Case Is = "val 1": myColor = 34
        Case Is = "val 2": myColor = 33
        Case Is = "val 3": myColor = 32
        Case Is = "val 4": myColor = 31
        Case Else
            myColor = xlNone
    End Select
    
    Target.Interior.ColorIndex = myColor
    
End Sub

'==========
everything from the "option explicit" to the "End Sub" should be copied|pasted.

But you've got some more work to do.

You'll have to add the values that you want to check and put the colors that you
want for each value.

"jladika <" wrote:
> 
> can you tell me what part of of this do i copy and paste
> from where to where joe
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/9/2004 1:55:17 AM
thanks for your help

when you say values 
does that mean the cells i need to be colored?

i need a column for this to be done i

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

0
6/9/2004 12:40:48 PM
This portion:

        Case Is = "val 1": myColor = 34
        Case Is = "val 2": myColor = 33
        Case Is = "val 3": myColor = 32
        Case Is = "val 4": myColor = 31

had colorindexes chosen almost at random.  I'd be willing to bet that you
wouldn't want to use 31-34 in your real code.

You could either record a macro when you set the colors of some test cell to see
what the numbers are or maybe visit David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/colors.htm

And besides, I only use 4 values and 4 colors!


"jladika <" wrote:
> 
> thanks for your help
> 
> when you say values
> does that mean the cells i need to be colored?
> 
> i need a column for this to be done in
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
6/9/2004 10:23:41 PM
Reply:

Similar Artilces:

Need help to read Pie Chart Series Range
Excel 2003, I have an existing Pie Chart and want to extract the Ranges (cells) used. My code below returns with a "Type mismatch" error. Sub GetPieChartSeries() Dim mySeries As Series ActiveSheet.ChartObjects(1).Activate Set mySeries = ActiveChart.SeriesCollection(1) Debug.Print mySeries.XValues (errors here) Debug.Print mySeries.Values End Sub Thanks, - Pat ...

Slow VBA macro
Every time the following code runs and it reaches a blank cell it takes 2-3 seconds to update the cell with a zero: Do Until ActiveCell.Offset(1, -3) = "" ActiveCell.Offset(1, 0).Range("A1").Select Select Case ActiveCell Case Is = "" Selection.FormulaR1C1 = 0 End Select Loop I've tried Application.ScreenUpdating = False but the macro is still very slow. Any suggestions as to ho...

need Help for Migration Exchange 5.5
Hello NG, we`ve the following Configuration : - Exchange 5.5 with SP4 - single Winwos 2000 DC - configured both side ADC a new Hardware should be used for an Exchange 2003 and second DC. next steps i would do as following : - Update ADC Version with Exchange 2003 ADC - running Exchange 2003 forestprep, then domainprep, an after all running setup to install Exchange 2003 in the same organization, site - replicate system Folders and public Folders - moving all Mailboxes to the Exchange 2003 Store - configure the Exchange 2003 virtual SMTP Server to send outgoing Mail - reconfigure the F...

Page set up #2
I want to use the paper size of A3, but cannot find it in the paper options. Anyone know where I can find it? Check your printer properties and preferences. If the printers you have installed won't handle A3 you won't have it. And you will need to "install" something that will. Even a "PDF" file printer will do. Alan Badgery wrote: > I want to use the paper size of A3, but cannot find it in the paper > options. Anyone know where I can find it? I've just a home use computer, but the intention was to use the work book between and home and work, w...

Override font setting
Hi, Can I override font setting when read a received email automatically? Because some font style very hard to read when received and I have to change font style one by one (sometimes cannot change) Thanks! William ...

Exchange 2003
Hello I have a newly setup production exchange server (sp2). It is only serving a small subset of my organisation, about 15 users, using mydomain1.com. My active directory contains around 100 user accounts that do not have exchange mailboxes and they use our old pop3 based system and have a seperate email domain name, mydomain2.com. It would appear that whenever someone with an exchange mailbox (mydomain1.com) sends mail to people with an email address listed elsewhere in the active directory (mydomain2.com) it gets stuck in the local exchnage delivery queue rather than forwarding i...

Word Doc Macro help needed please!
I need to make a mocro that will work in a word doc. This macro would start with [000001] and count up one number each paragraph. For example paragraph two would auto show [000002] and so on. Any help would be GREAT! I have a word file which contains 100 pages. All the pages have name & address of companies.(not in table) i have to transfer them to excel in column format(for example first name, last name, address, phone etc) can any one tell me code for this( i know how to open word & creat new excel sheet) . I need code for how to reach to lines of word. r there any thing like &quo...

Printing changes layout
I am printing a booklet on Pub 2002 - when I print one page, it prints a page where everything has changed - resized, cut off the edges, etc. The worst part - when done printing, it goes back to normal view and it actually changes my layout to match what is printed - permanently. Any ideas what is going on? I printed on both my Lexmark and HP printers, does the same thing. I have Windows XP. Help! Thanks! Look at your page layout, has it changed? I know this will happen to me occasionally when I change printers. If I change back to the original printer the publication will revert...

Event ID c0072030 #2
We have an Active Directory Organizational that contain all of our contacts. I have run the delegation of control wizard and given a user full control over the contacts OU. When I log on with that users account and try to update to add an SMTP address to a contact I receive the following error: There is no such object on the server Facility:Win32 ...

Help With Sorting #3
Gudday to all XL gurus. I need some help on using the SORT function. I have a list of 100 competitors in a scoring spreadsheet that I hav written. I was trying to sort by surname (a-z). The problem is that this list i generated elsewhere and contains VLOOKUP functions. Obviously if i hav less than 100 competitors I end up with some cells appear blank bu contain VLOOKUP functions. XL sorts them first and I want them sorte last after the surnames (a-z). I cant add a helper column as this lis relates to over 48,000 forumulaes and functions that are already added If I add a helper column I have...

Need help converting Microsoft Outlook calendar to Entourage.... At wits' end here! Please advise...
I have a Mac at work which runs Microsoft Outlook 2001 client on an Exchange server. I have a calendar on this machine (stored locally, not on the server) which has a ton of reminders programmed into it - very useful! Now, at home I got a new Mac laptop with Entourage X. Is there any way to move all of the reminders (appointments) from my calendar on Outlook to my Entourage calendar? I've tried two things: 1) I exported the calendar info as a CSV text file, and then tried importing it in Entourage. The problem is that Entourage import function seems to think it's importing an Address...

Excel Events Conflict with VB and C# AddIns
Hi, I have two com addins running in Excel. One is written in VB and the other in C#. Both addins are interested in the Workbookopen event. When the event is triggered the c# code captures the event but the VB code does not. If i disable the c# addin then the vb code works. Looks like the c# addin is somehow blocking the vb events. Anyone know how to fix this? Many thanks, Andy There is only one WorkbookOpen event, so once one add-in hooks it, there is nothing for the other one to hook. If you are hooking the WorkbookOpen event in your add-ins, this is likely your problem. You may need...

Canceling an OnClick Event
Hi all, Is it possible for a BeforeUpdate event of one control to cancel the OnClick event of another? Details: I have an unbound form that allows for editing and creating sites. txtSiteNo has a BeforeUpdate event that checks if the site already exists, and if it does, asks if you want to edit it. If so, the rest of the unbound text boxes update to what's already in the database. ctlAddSite has an OnClick Event which adds/edits the table info to the values of the rest of the unbound text boxes. Everything works fine, except that if someone enters an existing site int...

help with lookup formula (sheet 2, not a double post)
I need to make a formula using a lookup. The first spreadsheet is a inventory (ALO INVENTORY JP). The second (P&G 103) is a chart tha tells number of gallons of alcohol per foot/inch/fraction. On ALO INVENTORY JP I insert the # of feet, inches, and the fraction i cells R5 and T5. I want these numbers to lookup the number of gallon off of the P&G 103 worksheet. For example: P&G 103 (on inventory sheet) is 26ft 4 1/2 inches, i should look-up to be 34572 gal. for the 26'4" and 55 gal. for the 1/2" These two numbers should be added together and multiplied by the numbe ...

Help #15
I have a problem. I have a list of numbers that are in one column. Alot of numbers 8000 or so, and I need to find if there are an duplicates. I sort them so that they are in order, but having t scroll through the whole list takes alot of time and hurts my eyes!! I tried advanced filter but I can only find how to find duplicates in different columns. Someone help me please! ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step...

Help with displaying the contents of the last populate cell.
I have numerous sheets within a book where all cells in column C in all sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you reference both columns P and R hold a monetary value and are formatted as Currency. Is there a way that cell D1 can automatically be populated with the contents of the last cell in column C that has a value in it. E.G. Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200. Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250. Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900. Any h...

Help!! Way to find users sending email to large amount of receipients.
Could some one tell me if there is a way in Exchange 2003 to find out what users are sending email to a large amount of recipients? I'm having some serious performance issues and I'm sure it's some one sending email to a large amount of recipients. Thanks, Will ...

Help on filter
HI, I have a list to filter. I have to keep only integers. I tried everything in advanced filters... does not work. Can you help me please? Thanks Sylvai -- Message posted from http://www.ExcelForum.com Hi one way: use a helper column. Lets say your numbers are in column A then enter the following formula in an adjacent cell for row 2 =IF(MOD(A1,1)=0,"X","") copy this down for all rows. After this filter with this helper column -- Regards Frank Kabel Frankfurt, Germany > HI, > I have a list to filter. I have to keep only integers. > I tried everything in ...

help!
Hi I have been given a dataset with data,but the problem is now i need to create membership cards,which i plan to use the label wizard in reports,but i also have to display the intials of the customer but i have not been given the fields for intials just forename and last name.Is there maybe a way of running a query to search for some of the forename field to use as my intial???? thanks ! I thought I had seen this post a day or two ago and it was answered. If you want Initial and Last name Left(FirstName,1) & " " & LastName -- Dave Hargis, Microsoft Access MVP &qu...

Pointing to correct macro path using excel custom toolbar
I have created an excel 2000 template (.xlt) containing a number o macros. When I open copies of this template on various pcs, the macro function correctly, except I cannot successfully run the macros usin the custom toolbar I created, because (I think) within the toolbar th paths to the macros are pointed to the original location on my pc. An advice on how I can resolve this would be gratefully received -- Message posted from http://www.ExcelForum.com Have you thought about building the toolbar when the file opens? Or maybe separating the worksheet portion of the template from the code pa...

Enable macro #4
Is there a way that when a user opens an excel workbook that it will enable the macro without asking to disable or enable? I have users that click on disable, then the macro will not run. I have two macro buttons set up and they choose the one that is needed at this time. I would rather it open and the enable already be set without them having to click on it. Is this possible? No, Pam. Sorry. You can "force" them to enable. http://vbaexpress.com/kb/getarticle.php?kb_id=578 So, you could set it up that if they don't enable, they don't see... if they do enable, no...

Microsoft publisher, how do I set it to show multiple pages?
I'm trying to show more than one page in a single viewing pane. As in for the use of a banner. You'd think that'd be a simple and accesable function. I don't see it anywhere... Print preview has this function. Not sure if this is what you are asking... -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com http://officebeta.iponet.net/en-us/publisher/FX100649111033.aspx "BrickShort" <BrickShort@discussions.microsoft.com> wrote in message news:B42E0DAE-9DB6-4587-A157-3C2F5225FA02@microsoft.com... > I'...

Hide row code help
I am using the following script to hide or unhide rows. How do I code the offset to only work when a row is being unhid. If we cant do this only offset when it is being hidden or unhidden? Thank you Private Sub Worksheet_Change(ByVal Target As Range) 'Application.EnableEvents = False' not necessary If Not Application.Intersect(Target, Range("B9")) Is Nothing Then Rows("10:11").Hidden = IIf(UCase(Range("B9")) = "", True, False) 'spelling ActiveCell.Offset(-6, 0).Select End If 'Application.EnableEvents = False ...

Macro for Internet Order Window
I want to create a macro to launch the CTL-SHIFT-F10 "Internet order" button (because I am going to make a custom button for it). However, I cannot record the macro because as soon as I hit CTRL-SHIFT-F10, the order popup window pops up and I cannot end the macro by doing SHIFT-F3. If I close the window, then hit SHIFT-F3 to end the macro, the macro just opens and then closes the internet order window. Is there another way to do it? Thanks, mickie No ideas? No one has done this? Mickie wrote: > I want to create a macro to launch the CTL-SHIFT-F10 "Int...

two sets of parameters..one query...but how?
I have a query to track when I must renew State Police Clearances AND Child Abuse Clearances. I want to use the "Between [date] and [date]" function on BOTH of the fields in order to bring up BOTH sets of dates for BOTH clearances.The problem I am running into is that when the query results come up, if one date doesn't meet the criteria NONE of the information comes up (even if the other date DOES).Any suggestions!??!?! Thanks in Advance....