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
618 Views

Similar Articles

[PageSpeed] 45

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:

how do I set up an annual calendar on one worksheet
I need a tempplate to set up on one worksheet an annual calendar with days of the week, months and date numbers. I havae to set up a calendar that has recurring dates e.g. evry 3rd Weds in Feb, 4th Tues in may etc ANy help would be appreciated on all or some of the above start here: http://office.microsoft.com/en-us/results.aspx?Scope=TC&Query=calendar -- Gary''s Student "Ian Dee" wrote: > I need a tempplate to set up on one worksheet an annual calendar with days of > the week, months and date numbers. > I havae to set up a calendar that has recurring ...

Money 2004
For some reason my transactions are being categorized but not tracked in one account. It's an AEX account which I use for most of my purchases. I have no idea what I did, but now, when I enter a transaction with category, etc...my thermometer doesn't register the additional transaction and it doesn't show in my budget! When I enter the same transaction in any other account, it shows. Anyone know how to fix this? It was working fine, but I must've done something. Appreciate any response. Phil Roos Go figure...after searching for hours for a solution...me typing this po...

Add to Formula
I have created a drop down list and added a formula to show me the cost of an item eg; pink slippers when that is picked from the list the price appears in the next column..that is all working fine.....=IF(C3="","",VLOOKUP(C3,'Sheet3 (2)'!A3:C152,2,FALSE)) I've been trying to add another formula to this for inventory. So actually what my question is. How do I create a list along with a formula so when I pick the item out it will put in the price, and minus 1 or however many sold, to give me a count of what I have left in stock, and say I started with 25 of...

Problem with code
Dear all please see below a previous post made and a resolution posted however this code is returning an error. Can anyone see where this error is. Also do I paste this code into a module or not Thank Ada --------------------------------- Hi, Adam Not impossible at all. Why not date-stamp each use entry in a column for each data entry row, so you ca create a little macro to clear all old dates something like Private Sub Worksheet_Change(ByVal Target As Range If Target.Cells.Count > 1 Then Exit Su If Intersect(Target, Me.Range("C1:C5")) Is Nothing Then Exit Su Application.En...

Can you set up another email account in an early v of Outlook?
I need to set up an second email account in Outlook. This would be fine if they had the latest version but its not and on a Win95 machine. So can this be done? I can't even put Outlook Express v6 on as it doesn't like 95. Thanks Jen wrote: > I need to set up an second email account in Outlook. This would be > fine if they had the latest version but its not and on a Win95 > machine. So can this be done? I can't even put Outlook Express v6 on > as it doesn't like 95. > > Thanks What version of Outlook? ...

Anti-crack and anti-piracy application protection and licensing solution using code morphing
Software piracy! Cracked serial numbers! Thousands of commercial products are posted on the warez sites and become available to all every day! Companies lose millions of dollars every year to software piracy, and faulty protection programs. Shareware developers look for unbreakable protection for their products and create some protection themselves or try many of the ready-made tools. Unfortunately most tools have already been cracked, and self solutions often only take one determined cracked a few hours to bypass. As a result they soon find the stoles keys and product cracks on thousands of ...

Another NameTable Question
I am trying to work out the best way to use the NameTable class in my C# application. I am assuming that getting/adding a string to the nametable has the same overheads as comparing a string normally, so I have created a class which holds references to the atomized strings. Is there a better way to do this? simple example below //Class to provide "string constants" private class MyStrings { public String stringA; public String stringB; public MyStrings(NameTable nt) { stringA = nt.Add("A"); stringB = nt.Add("B"); } } //Class...

View one record at a time in a report.
I am new to access. I created a report from a table using the wizard. When I view the report, I only want to see one given record at a time - not all records. How do I do this? Thank you Ron The easiest way is to start from a form, where you bring up the record you want to see. Then put a command button on the form, to open the report to just that one record. Here's the code you need for the command button: Print the record in the form at: http://allenbrowne.com/casu-15.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne...

exporting Outlook Express on one PC...
to Outlook on a PC Tablet How does one go about exporting then importing? Do you need a separate program to do this? You must follow instructions for transferring from one installation of OE to the other first. Only then can you import into Outlook. -- Russ Valentine [MVP-Outlook] "Paul Currie" <pwc@sasktel.net> wrote in message news:126ces4t34q6f30@corp.supernews.com... > to Outlook on a PC Tablet > How does one go about exporting then importing? Do you need a separate > program to do this? ...

Audit Trail Code
I have the following code to help with an audit trail in Access 2007 but it keeps bringing me back to the code below when I try to save my form. It highlights Call LogError but the first and second lines are in all yellow. I am a novice and have no clue of what all that means. I copied the code of course. Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _ lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean On Error GoTo Err_AuditEditBegin 'Purpose: Write a copy of the old values to temp table. ' It is th...

Formula for current month minus one = Quarter number in a macro.
I have a macro that does several things. The last step should be to save the file to a network drive with a name and quarter number appended to it. The file save command I have got is: - ActiveWorkbook.SaveAs Filename:= _ "Y:\Skip Register\Quarterly Charging Period ?", FileFormat:=xlExcel9795, _ Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False The ? in the file name should be substituted by the formula as described below. The macro is to be run in the month following a quarter (i.e. run macro in April to get Quar...

Need VB5 code for these VB6 instructions
I am trying to use the vbSendMail.dll (written in VB6) in a VB5 project. Need Vb5 code for: Private WithEvents poSendmail as vbSendMail.clsSendmail Private Sub Form_Load() Sdet poSendMail = New clsSendMail End Sub Help!! On Tue, 26 Jan 2010 13:33:01 -0800, Dennis Rose <DennisRose@discussions.microsoft.com> wrote: >I am trying to use the vbSendMail.dll (written in VB6) in a VB5 project. >Need Vb5 code for: > >Private WithEvents poSendmail as vbSendMail.clsSendmail > >Private Sub Form_Load() > Sdet poSendMail = New clsSendMail >...

How to refer to current sheet in a formula
I want to have a named formula which always refers to a cell on the sheet it is on. When I define the formula it is automatically changed to refer to the sheet active when I defined it. E.g. =$B$2*3 becomes =sheet1!$B$2*3. This is no good for use on sheet 2 where I want the formula to refer to that sheet's $B$2 (equivalent of sheet2!$B$2). I have tried entering =!$B$2*3 which works initially but recalculates using the $B$2 on the sheet active when the recalculation is done. -- Poxypig ------------------------------------------------------------------------ Poxypig's Profile: http:/...

Inconsistent outgoing messages to one domain
Greetings. Since we've installed SP2 on Microsoft Exchange 2003, we are having problems emailing a particular domain. If I use the Message Tracking Tool to monitor the email status we get two results. The first is, the last line has "This message transferred to <server name> through SMTP (1031). The other result is, "This message transferred to through SMTP (The server name is missing). If the message has the server name in it, the message is being sent and recieved, but if the line doesn't have the server name the message may or may not be delivered. Also, if we...

Two domains and two users
(1) I have domain A and domain B (2) I have user A in domain A and user A in domain B (basically same user name but created at different time - different SIDS) (3) I want to remove domain A but copy user A SID info into user A of domain B - basically merge the SID of two accounts into domain B Question: ======= (1) How do I merge two accounts together into one account - merge SIDS of two accounts into one account? I HAVE NO Option such as delete destination account and recreate so PLEASE do not provide me that alternative or explanation - even if it is the easiest. Thank you k...

Add Lead Source
This sounds simple but I can't seem to find a solid answer in Help or online. How do I add Lead Sources to the "Lead Source" drop down on the lead creation page? Thanks -David you will need to customize the form. You will need admin rights: go to settings, system customizations, lead, customize form. Then navigate to the field to open and make changes to the dropdown values. You then need to publish changes and do iisreset to see. Good luck! >-----Original Message----- >This sounds simple but I can't seem to find a solid answer in Help or >online. ...

How to create and access (add/delete mails) .PST file at runtime?
Hi, I need to store selected mails into some different .pst file at runtime. Also want to access those mails in .pst at runtime. I can import and export mails into .MSG file. But it is hard to maintain mails as .msg files. My queries are, 1. How to create/open .PST file at runtime ? 2. How to add/delete (import/export) mails (any format like .MSG, ..EML, etc.,) into .PST at runtime ? Is there any way to handle this kind of situation thru Outlook object model or CDO or Extended MAPI or Redemption or any other way? Thanks in advance with best regards, Kumar 1. Use Namespace.AddStore/Re...

VB Code for button for Access 2003
I have a form called "Quote Entry" displaying data from a table called "Quote" with a field "CustomerID", on this form there is a button called "Customers". This opens a form called "Customer Display" which is a Continuous list of customers, from the current selected customer record. I need some code for a button on the "Customer Display" form to save the current CustomerID back the Quote Entry form and add it to the Quote Table -- thank you for your help "Meaty" <Meaty@discussions.microsoft.com> wrote in messa...

lookup from one column return value from another?
I want to check the value of one column (A) and then depending on the outcome add the value in the same row but column B to a total. Can I do this in a single cell formula or do I have to have an extra column that does the logic test and then a cell to do the totalling? Hi Maria not sure of exactly what you want but does =IF(A1="Fred",B1,0) give you what you want? if not could you type out an example of your data and what you want to see (please don't attach a workbook just type it out) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working...

Multiple logins
Here is the scenario: My Fiancee & I share some of the same accounts. When she logs into the bank, she can see our joint accounts plus her personal accounts. The same for me. The issue is when I set Money up to pull from the bank, I get two of the same account. If I try to delete one of the duplicates, it tells me it has to delete all accounts from that login (including the history). I do not want to do this, but what can I do to save my sanity? Thanks! ...

Can one access newsgroups from Outlook?
thanks In a way, yes. It calls for Outlook Express for this action. see: Outlookhelp - Support for Microsoft Office Outlook http://www.howto-outlook.com/Faq/news.htm "d p" <dp25@access-4-free.com> wrote in message news:%23ZttKlS9EHA.2196@TK2MSFTNGP14.phx.gbl... > thanks > > Thanks so much Walt ... a life saver. "Walt Basil" <see_signature@for_address.com> wrote in message news:#WV4VzS9EHA.2676@TK2MSFTNGP12.phx.gbl... > In a way, yes. It calls for Outlook Express for this action. > > see: > Outlookhelp - Support for Microsoft Offic...

Just to add my 5c
In article <ee86e23.11@webcrossing.caR9absDaxw>, Shay <> wrote: > I don't know if Mac OS X has 'just' acquired proper RTL support Who are you talking to? I think you must have the wrong news group. -- Note: Please send all responses to the relevant news group. If you must contact me through e-mail, let me know when you send email to this address so that your email doesn't get eaten by my SPAM filter. JR ...

CListCtrl: Changing height of currently selected item?
What I want to do is a list like the one in Windows Control Panel -> Software, where the currently selected list item is shown with more detailed information. My core problem is: How to change the height of the currently selected list item so that more detailed information can be displayed? Any ideas how to realize this easily (i.e. apart from doing the *complete* drawing of the list items by myself using CustomDraw)? Thanks, Harald hi u give one image list to the list control with regards Harish "Harald Karner" <harald_karner@a1.net> wrote in message news:c12cv8$1...

Add Custom Activity Type
I have been asked to add a custom actiivity type to the activities entity called Site Visits. Can I get some direction as to how to do this? Afraid, you can't add custom activity types. You can create the entity, but it will behave like any other and does not integrate into the activity views and activity dialogs. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dialog: http://www.stunnware.com/crm2/?area=customLookup ---------------------------------------------------------- "Swamy&qu...

Validation of Postal Code
Hi; is there a way to validate CND postal codes in a spreadsheet? If not how can I format the column to accept only A9A9A9 type of data? (Upper case, number,uppercase, etc.) Thank you, Veronika You can use the custom data validation formula that J.E. McGimpsey shows in this message: http://groups.google.ca/group/microsoft.public.excel.misc/msg/4e93ebdb2d254817?hl=en& If you want to check for upper case, you can add the Exact function to the formula. However, the formula would then be too long for the data validation formula box: =AND(EXACT(A1,UPPER(A1)),(LEFT(A1)>=&quo...