| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do add another code to a current one?
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do add another code to a current one?
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do add another code to a current one?
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" wrote in message ... 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How do add another code to a current one?
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" wrote in message ... 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA code relative to current date | Excel Programming | |||
| code that searches for current date | Excel Programming | |||
| Referencing code outside of current sheet | Excel Programming | |||
| Code for current sheet selected... | Excel Programming | |||
| My current code | Excel Programming | |||
Linear Mode
