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

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

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

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



.

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



.