How do add another code to a current one?

I have this following code to make the rows changed based on the critea in 
column 16, and I need add A "Red, Yellow, Green" for status to only one 
column 30 at the end of the spreadsheet. How do I add another code? I keep 
getting an error..

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range, clr As Long
    For Each c In Target.Cells
        If c.Column = 16 Then

            Select Case c.Value
                Case "Analyze": clr = RGB(204, 255, 255)
                Case "Build ": clr = RGB(204, 255, 255)
                Case "PDP": clr = RGB(204, 255, 255)
                Case "Pending Requirements Review": clr = RGB(204, 255, 255)
                Case "Requirements Verified": clr = RGB(204, 255, 255)
                Case "Testing": clr = RGB(204, 255, 255)
                Case "Installed-In Production": clr = RGB(201, 153, 255)
                Case "In-Progress Pending Verification": clr = RGB(201, 153, 
255)
                Case "Cancelled": clr = RGB(128, 0, 0)
                Case "On-Hold": clr = RGB(255, 255, 153)
                Case "On-Hold Pending Resource": clr = RGB(255, 255, 153)
                Case "On-Hold Pending Requirements": clr = RGB(255, 255, 153)
                Case Else: clr = RGB(255, 255, 255)
            End Select

            With c.EntireRow.Cells(1).Resize(1, 26)
                .Interior.Color = clr
            End With

        End If
    Next c
End Sub

0
Utf
12/17/2009 6:49:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
564 Views

Similar Articles

[PageSpeed] 21

You can have only one Worksheet_Change event code per sheet, but you can 
have several different actions performed within that code by using If...Then 
statements to determine which part of the code executes:

exmpl:

If Not Intersect(Target, Range("O:O") Is Nothing Then
     'do one task for column 16
End If
If Not Intersect(Target, Range("AD:AD") Is Nothing Then
    'do a task for  column 30
End If

With the above code the target cell would have to be in column 16 for the 
first part to execute or in column 30 for the second part to execute, 
otherwise it does not execute at all.
But it can execute one part without executing the other depending on the 
location of the target cell.


"kwitt" <kwitt@discussions.microsoft.com> wrote in message 
news:0CF08F91-DEAE-4A5C-9C1E-83A0000920DA@microsoft.com...
>I have this following code to make the rows changed based on the critea in
> column 16, and I need add A "Red, Yellow, Green" for status to only one
> column 30 at the end of the spreadsheet. How do I add another code? I keep
> getting an error..
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>    Dim c As Range, clr As Long
>    For Each c In Target.Cells
>        If c.Column = 16 Then
>
>            Select Case c.Value
>                Case "Analyze": clr = RGB(204, 255, 255)
>                Case "Build ": clr = RGB(204, 255, 255)
>                Case "PDP": clr = RGB(204, 255, 255)
>                Case "Pending Requirements Review": clr = RGB(204, 255, 
> 255)
>                Case "Requirements Verified": clr = RGB(204, 255, 255)
>                Case "Testing": clr = RGB(204, 255, 255)
>                Case "Installed-In Production": clr = RGB(201, 153, 255)
>                Case "In-Progress Pending Verification": clr = RGB(201, 
> 153,
> 255)
>                Case "Cancelled": clr = RGB(128, 0, 0)
>                Case "On-Hold": clr = RGB(255, 255, 153)
>                Case "On-Hold Pending Resource": clr = RGB(255, 255, 153)
>                Case "On-Hold Pending Requirements": clr = RGB(255, 255, 
> 153)
>                Case Else: clr = RGB(255, 255, 255)
>            End Select
>
>            With c.EntireRow.Cells(1).Resize(1, 26)
>                .Interior.Color = clr
>            End With
>
>        End If
>    Next c
> End Sub
> 


0
JLGWhiz
12/17/2009 7:20:43 PM
Where are you getting the error?  I'm not getting an error.
-- 
Cheers,
Ryan


"kwitt" wrote:

> I have this following code to make the rows changed based on the critea in 
> column 16, and I need add A "Red, Yellow, Green" for status to only one 
> column 30 at the end of the spreadsheet. How do I add another code? I keep 
> getting an error..
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
>     Dim c As Range, clr As Long
>     For Each c In Target.Cells
>         If c.Column = 16 Then
> 
>             Select Case c.Value
>                 Case "Analyze": clr = RGB(204, 255, 255)
>                 Case "Build ": clr = RGB(204, 255, 255)
>                 Case "PDP": clr = RGB(204, 255, 255)
>                 Case "Pending Requirements Review": clr = RGB(204, 255, 255)
>                 Case "Requirements Verified": clr = RGB(204, 255, 255)
>                 Case "Testing": clr = RGB(204, 255, 255)
>                 Case "Installed-In Production": clr = RGB(201, 153, 255)
>                 Case "In-Progress Pending Verification": clr = RGB(201, 153, 
> 255)
>                 Case "Cancelled": clr = RGB(128, 0, 0)
>                 Case "On-Hold": clr = RGB(255, 255, 153)
>                 Case "On-Hold Pending Resource": clr = RGB(255, 255, 153)
>                 Case "On-Hold Pending Requirements": clr = RGB(255, 255, 153)
>                 Case Else: clr = RGB(255, 255, 255)
>             End Select
> 
>             With c.EntireRow.Cells(1).Resize(1, 26)
>                 .Interior.Color = clr
>             End With
> 
>         End If
>     Next c
> End Sub
> 
0
Utf
12/17/2009 7:36:02 PM
If I want keep the current code that is based on the criteria of column 16 
and changes the color from column 1 - 29. How and where do I add this new 
code for column 30. Instead of having 1 target cell as 16 can I make it 31 
which is not populated in our spreadsheet? Or do I need change the current 
code as well?? 

"JLGWhiz" wrote:

> You can have only one Worksheet_Change event code per sheet, but you can 
> have several different actions performed within that code by using If...Then 
> statements to determine which part of the code executes:
> 
> exmpl:
> 
> If Not Intersect(Target, Range("O:O") Is Nothing Then
>      'do one task for column 16
> End If
> If Not Intersect(Target, Range("AD:AD") Is Nothing Then
>     'do a task for  column 30
> End If
> 
> With the above code the target cell would have to be in column 16 for the 
> first part to execute or in column 30 for the second part to execute, 
> otherwise it does not execute at all.
> But it can execute one part without executing the other depending on the 
> location of the target cell.
> 
> 
> "kwitt" <kwitt@discussions.microsoft.com> wrote in message 
> news:0CF08F91-DEAE-4A5C-9C1E-83A0000920DA@microsoft.com...
> >I have this following code to make the rows changed based on the critea in
> > column 16, and I need add A "Red, Yellow, Green" for status to only one
> > column 30 at the end of the spreadsheet. How do I add another code? I keep
> > getting an error..
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >    Dim c As Range, clr As Long
> >    For Each c In Target.Cells
> >        If c.Column = 16 Then
> >
> >            Select Case c.Value
> >                Case "Analyze": clr = RGB(204, 255, 255)
> >                Case "Build ": clr = RGB(204, 255, 255)
> >                Case "PDP": clr = RGB(204, 255, 255)
> >                Case "Pending Requirements Review": clr = RGB(204, 255, 
> > 255)
> >                Case "Requirements Verified": clr = RGB(204, 255, 255)
> >                Case "Testing": clr = RGB(204, 255, 255)
> >                Case "Installed-In Production": clr = RGB(201, 153, 255)
> >                Case "In-Progress Pending Verification": clr = RGB(201, 
> > 153,
> > 255)
> >                Case "Cancelled": clr = RGB(128, 0, 0)
> >                Case "On-Hold": clr = RGB(255, 255, 153)
> >                Case "On-Hold Pending Resource": clr = RGB(255, 255, 153)
> >                Case "On-Hold Pending Requirements": clr = RGB(255, 255, 
> > 153)
> >                Case Else: clr = RGB(255, 255, 255)
> >            End Select
> >
> >            With c.EntireRow.Cells(1).Resize(1, 26)
> >                .Interior.Color = clr
> >            End With
> >
> >        End If
> >    Next c
> > End Sub
> > 
> 
> 
> .
> 
0
Utf
12/18/2009 3:38:02 PM
Just change your If...Then statement to this.

If c.Column = 16 Or c.Column = 30 Then

Hope this helps!  If so, click "YES" below.
-- 
Cheers,
Ryan


"kwitt" wrote:

> If I want keep the current code that is based on the criteria of column 16 
> and changes the color from column 1 - 29. How and where do I add this new 
> code for column 30. Instead of having 1 target cell as 16 can I make it 31 
> which is not populated in our spreadsheet? Or do I need change the current 
> code as well?? 
> 
> "JLGWhiz" wrote:
> 
> > You can have only one Worksheet_Change event code per sheet, but you can 
> > have several different actions performed within that code by using If...Then 
> > statements to determine which part of the code executes:
> > 
> > exmpl:
> > 
> > If Not Intersect(Target, Range("O:O") Is Nothing Then
> >      'do one task for column 16
> > End If
> > If Not Intersect(Target, Range("AD:AD") Is Nothing Then
> >     'do a task for  column 30
> > End If
> > 
> > With the above code the target cell would have to be in column 16 for the 
> > first part to execute or in column 30 for the second part to execute, 
> > otherwise it does not execute at all.
> > But it can execute one part without executing the other depending on the 
> > location of the target cell.
> > 
> > 
> > "kwitt" <kwitt@discussions.microsoft.com> wrote in message 
> > news:0CF08F91-DEAE-4A5C-9C1E-83A0000920DA@microsoft.com...
> > >I have this following code to make the rows changed based on the critea in
> > > column 16, and I need add A "Red, Yellow, Green" for status to only one
> > > column 30 at the end of the spreadsheet. How do I add another code? I keep
> > > getting an error..
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > >    Dim c As Range, clr As Long
> > >    For Each c In Target.Cells
> > >        If c.Column = 16 Then
> > >
> > >            Select Case c.Value
> > >                Case "Analyze": clr = RGB(204, 255, 255)
> > >                Case "Build ": clr = RGB(204, 255, 255)
> > >                Case "PDP": clr = RGB(204, 255, 255)
> > >                Case "Pending Requirements Review": clr = RGB(204, 255, 
> > > 255)
> > >                Case "Requirements Verified": clr = RGB(204, 255, 255)
> > >                Case "Testing": clr = RGB(204, 255, 255)
> > >                Case "Installed-In Production": clr = RGB(201, 153, 255)
> > >                Case "In-Progress Pending Verification": clr = RGB(201, 
> > > 153,
> > > 255)
> > >                Case "Cancelled": clr = RGB(128, 0, 0)
> > >                Case "On-Hold": clr = RGB(255, 255, 153)
> > >                Case "On-Hold Pending Resource": clr = RGB(255, 255, 153)
> > >                Case "On-Hold Pending Requirements": clr = RGB(255, 255, 
> > > 153)
> > >                Case Else: clr = RGB(255, 255, 255)
> > >            End Select
> > >
> > >            With c.EntireRow.Cells(1).Resize(1, 26)
> > >                .Interior.Color = clr
> > >            End With
> > >
> > >        End If
> > >    Next c
> > > End Sub
> > > 
> > 
> > 
> > .
> > 
0
Utf
12/21/2009 1:30:01 PM
Reply:

Similar Artilces:

automatically add email address to contacts when replying mail in outlook 2003 #3
every time i send a reply or email to sometime how can i tweak outlook 2003 to save the email address to my contacts automatically?? - Thank you Mayur Mayur Patel <patelmb@vt.edu> wrote: > every time i send a reply or email to sometime how can i tweak > outlook 2003 to save the email address to my contacts automatically?? http://www.slipstick.com/contacts/addauto.htm -- Brian Tillman ...

Input mask
Can anyone please help me with setting an input mask for a cell phone field in the format +00 00 111 2222 or +00 00 111 222. Would really appreciate some guidance on this. ...

how does one become an excel beta testor
i herd the new version of office in beta is do out next month - how cqn I sign up to be a testor. I am really only interested in excel - If I could get a hold of this new version of excel with it's 1.1 million rows i could shut down allot of plans to go to third party software at my firm and make much better use of excel. ( excel & access combined) I woul dprimarilyy be makin gextensive and advanced use of pivot tables. I would be glad to particpate in any feedback programs that thi sopportunity would require. Thanks for your time ...

simple guide to merging 2 worksheets into one request
Hi, I am a basic excel user with little knowledge! I have been asked to merge 2 worksheets together using a unique ID as the method to combine data, for example, WS 1 - membership no - address - phone no WS 2 - membership no - name - date of birth Required WS3 - membership no - name -address -phone no - date of birth I have found lots of VB code but to be honest I have no idea what it is, where it needs to go on the workbook or how to even start to use it. Is there a very simple way to achieve this other than cut and paste (I have over 10k records)? If someone code provide an idiot...

How do I add musical score (adding my own notes) to publisher
The intention is to add trebel or base musical score to MS Publisher brochure and be able to add notes to it, i.e. I wish to reproduce a hymn in a church bulletin. SB wrote: > The intention is to add trebel or base musical score to MS Publisher brochure > and be able to add notes to it, i.e. I wish to reproduce a hymn in a church > bulletin. Microsoft Publisher is not a scorewriting program. Technically, you could put in some ClipArt musical notes or WordArt/text if you have the fonts available, but you would be much better off using a dedicated scorewriter and then exporting...

How to add a disclaimer at the bottom of all outgoing messages
I would like to add a standard text at the bottom of every message sent to external address. I have both Exchange 2000 and 2003. I do not want to use the signature feature in Outlook, but prefer to do it at the exchange level which will apply to all users. Hi You have two options here, you can either Follow MS article q317608 to create an SMTP Transport Event Sync, see here: http://support.microsoft.com/?scid=kb;en-us;317680 Second option: purchase a product such as GFI MailEssentials Regards -- Ben Hoffman MCP (Win2k Pro&Svr, Exchange 2003 Admin) http://www.ExchangeIS.com "...

Access 2007 ControlSource code in Report causing shut down
The following code worked successfully in Access 2002 & 2003 but for some reason, the lines that contain ControlSource are causing Access 2007 to shut down and error out. Any suggestions you have would be greatly appreciated. In short, I’m using a combo box on a form “ReportCenter” to change the GroupLevel and textbox values on a report. If I comment out the lines with ControlSource the code runs successfully, if any one of them are uncommented, Access 2007 shuts down and restarts. 'Group 1 If Forms![ReportCenter]!T2GroupCheck1 = True And Forms![ReportCenter]!T2GroupDescendC...

"MSXML2.ServerXMLHTTP" add 'HTTP' in front of all request parameter
Hi, I need to write a ASPX file that will post some request to other party (web service written in java), here is my code, <%@ Page aspcompat=true %> <% Dim xml, url xml = Server.CreateObject("MSXML2.ServerXMLHTTP") url = "http://localhost/Testing.aspx" xml.Open ("POST", url, False) xml.SetRequestHeader ("status","1" ) xml.SetRequestHeader ("ref_id","5354" ) xml.SetRequestHeader ("response_type","MT" ) xml.Send () Response.Write (xml.responsetext) xml = not...

Two formulas in on cell based on two numbers in another cell?
Hi, Not sure this is possible but...I have a cell that has a number range in it and based on an amount in another cell want to calculate a new range. For example: Initial Range: A1 = 10 - 12 Calc Amount: A2 = 5 Final Range: A3 = 50 - 60 I think I can get the results by concatenating two formulas I'm just not sure how to enter the original numbers (A1) or how to distinguish between the two in the final formula (A3) Using Excel 2003. Hope this makes sense. Thanks. I would put the range in two different cells (eg A1 and B1). Then the multiplication is easy. If you ...

how do i merge one workbook with another in excel?
hi i am wanting to merge data from 1 worksheet with another how do i do this? Copy/paste then manipulate. OR provide more detail on your layout and type of data to merge. Gord Dibben Excel MVP On Wed, 16 Mar 2005 11:33:04 -0800, "dd" <dd@discussions.microsoft.com> wrote: >hi i am wanting to merge data from 1 worksheet with another how do i do this? hi there thanks for your reply. i do not have exact data. it is for an interview question how do you merge spreadsheet A and spreadsheet B using excel ? and then how do you identify say for instance someones name who is ...

How do I take two pictures put them side by side to create one
I am trying to create one picture by placing 2 of them side by side. Use a photo editing program then insert the picture into Publisher. -- JoAnn Paules MVP Microsoft [Publisher] "coopskat679" <coopskat679@discussions.microsoft.com> wrote in message news:6EEB18D4-FB70-40AE-9CB6-AD40F8FD80A7@microsoft.com... >I am trying to create one picture by placing 2 of them side by side. coopskat679 wrote: > I am trying to create one picture by placing 2 of them side by side. Insert two pictures. Turn on the Snap to Objects option (Arrange > Snap > To Objects). ...

Conditional Formating for a cell based on another cell's value
Hi all: I was wondering if there was a way to put a conditional formating on a cell based on the values of another cell. What I mean is that, if Cell A is >5 , make cell b Blue, not just cell A. Likewise, if Cell A is <5, make both cell a and b red. On the same topic, is it possible to put more than 3 conditions on a conditional formating? Thanks, Steve o This might point you in the right direction. Sometimes you need a row formatted according to the item in A1. Use a formula in the conditional formatting in type in something like this... =$A1<5 This will apply the cond...

splitting text from one cell
I need to remove specified data from a single cell and place on the same row in another column. Not all data will be the same eg 100mg, 200mg etc Not too clear; what data? Do you want to extract the number from the text? This will extract 100 from 100mg and 5 from 5mg: =--LEFT(A1,FIND("m",A1)-1) Any help? -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "rogera" <rogera@discussions.microsoft.com> wrote in message news:98106D4C-04D2-4A2D-B293-9F5BBF0F04EB@microsoft.com... >I need to remove specified data from a single cell and place on th...

Linking rows of data to another worksheet
Worksheet One contains survey data pertaining to customer satisfaction for all of our building communities. The data is entered in each row as follows.. .. community name, lot number, buyer name, etc. Therefore, this worksheet contains all the survey results for all of our buyers, and then based on the survey responses, an overall rating is calculated. I would like to then link each row to its corresponding worksheet per community. By doing this, I can calculate the survey ratings per community as opposed to the overall rating calculated on worksheet One. I would greatly appreciate any as...

Forward selected attachments from another email
I received an email with 10 attachments, and I want to forward 5 of them to someone. How can I select the attachments and send in a new email? I am using Outlook 2003. In Outlook 2000, I can do <Ctrl> + highlight the attached files, but can't do it in 2003. Any suggestions? "Tiffany" <tiffany@vpvp.com> wrote in message news:%23azaniUFFHA.624@TK2MSFTNGP15.phx.gbl... >I received an email with 10 attachments, and I want to forward 5 of >them to > someone. How can I select the attachments and send in a new email? I > am > using Outlook 2003. In...

Add Records
I have a database to which now for some unknown reason (probably really simple) it no longer allows me to add a new record on the form, only the table. I have AllowDataEntry set to yes and I dont recall changing any properties. Is there something I am overlooking or why would it I be unable to add a new record on the form? Please help. Is Allow Additions set to Yes? Make sure the Recordset Type is set to Dynaset. If you want to see existing records, set Data Entry to No. You might also check the form's RecordSource is the table (i.e. not a non-updatable query.) -- Allen Browne ...

Add Record to Subform using a Listbox
I have a Form with a Subform. The Form contains a multi-select List Box for selection of records to add to the Subform. I can’t seem to get the code right to accomplish this. Any help would be very much appreciated. FrmEvents (contains List Box: “lstDefs” & subform: “frmSubEventDef”) Record Source: q_frm_event Primary key: EventNo lstDefs (Unbound) Field 1: DefNo Field 2: Defintion Multi-Select: Extended frmSubEventDef Record Source: q_frm_event_defs Field 1: EventNo Field 2: DefNo Link Child Fields: EventNo Link Master Fields: EventNo On selection of specific row(s) in lstDefs, th...

vba code #6
why does this code not input a formula into cells e5 to end of data Range("D6").Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1").Select Do Until Range("E5").Select ActiveCell.FormulaR1C1 = "=R2C8/(RC[-2]-RC[-4])" ActiveCell.Offset(-1, 0).Range("a1").Select Loop Hi agw, Try the following change: Do Until ActiveCell.Address = "$E$5" Ken Johnson It looks like you're using column D to get the last used cell. If that's true: with activesheet .range("E5:E" &am...

Can I add a new tax "form line"?
I have Money 2004 (Small Business Edition). There is a relatively new line on the 1040 called Self-Employed Health Insurance Deduction (line 29) that doesn't exist in Money. Unfortunately, when I try the "add a tax line" function (in the Tax Line Manager), it forces me to select one of the pre-existing "form line" options for the 1040 form. I'm trying to figure out how I can update this list with a new form line for this item. I've tried doing the tax update on the tax settings page, but it doesn't help. Any ideas? In microsoft.public.money, c...

Selecting printer from code
hi I have buttons on my forms that cause various reports to be printed. But, some of the reports are to be printed on one printer, and some on another printer. Both are connected to the computer. How do I do this? It guess it would be OK for the code to pull up the Print window, but I'd like it best if the code can actually select the correct printer. (Lexmark E330 or Canon i550) Any clues appreciated. -- Thanks Sophie You can have two reports, one set to print to one specific printer, the other (duplicate) report set to print to the second printer. But, then you have to m...

add accents to text
I want to add accents to first/last names in a spreadsheet I'd also like to be able to add scientific symbols to text in a spreadsheet. From your START button go to Programs/Accessories/System Tools/Character Map Slow way: use Select/Copy then Paste into Excel Faster way: click on the accented letter you want, look at bottom right corner (eg for ï {as in naïve} you'll see "Keystroke: Alt + 0239). Do this for all the accented letters you think you will need. Keep the paper handy and use the Alt + four digit code to obtain your results. Stephen Powell "mtambeau...

Transfer from one bank to another
I'm a new MSMoney user and just set up a new bank account. I wrote a check from one existing account to the new account in a different institution. How should I catagorize the transfer of funds? MSMoney, in the budget tracking, etc. seems to show the the transfer as income (but to me it's not new dollars). Any suggestions? In microsoft.public.money, PMHicks wrote: >I'm a new MSMoney user and just set up a new bank account. I wrote a check >from one existing account to the new account in a different institution. How >should I catagorize the transfer of funds? ...

Using code to insert different formulas
[Excel 2003] I have sixteen different conditions which can exist. These relate to whether certain aspects are TRUE or FALSE. When taken together I have 16 unique "cases". These appear in cells A1:A60 and are labeled Condition1, 2, 3 etc... Condition16 Depending on the condition in each cell in column A, I need certain formulas to operate in adjacent cells in colomn B and C For example for Condition1 in A1, I need the formula in B1 to be "=D1*E1" and the formula in C1 to be "F1*G1/H1" Each formula referes to cells in the same row, so perh...

word 2010 crashes when trying to add digital signature to documen
Have not been able to add digital signatures to word 2010 beta documents ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=75799873-9165-496a-84a8-b4dd0603811b&dg=microsoft.public.word.docmanage...

Removing Add Inns
I have an Essbase Add In installed in my version of Excel. Is there a way to remove it. I don't use it. Uncheck under tools>add-ins and if it is in a startup folder remove it -- Regards, Peo Sjoblom "Leon" <greg_wikle@balinet.com> wrote in message news:emEVXwP5DHA.1804@TK2MSFTNGP12.phx.gbl... > I have an Essbase Add In installed in my version of Excel. Is there a way to > remove it. I don't use it. > > ...