Can two worksheet change function be used?

[js1537]

Hi,

I am using two worksheet change function in my below vba coding. But getting ambiguity error while running code. Please assist.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)</SPAN></SPAN>
If Intersect(Target, [b7]) Is Nothing Then Exit Sub</SPAN></SPAN>
Select Case [b7].Value</SPAN></SPAN>
Case "Existing GCIS Group - No Changes": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 1</SPAN></SPAN>
Case "Close GCIS Group": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 1</SPAN></SPAN>
Case "Existing GCIS Group - Changes": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0</SPAN></SPAN>
Case "New to GCIS": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0</SPAN></SPAN>
'Case Else: Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0</SPAN></SPAN>
End Select</SPAN></SPAN>
End Sub</SPAN></SPAN>

Private Sub Worksheet_Change(ByVal Target As Excel.Range)</SPAN></SPAN>
If Intersect(Target, [b46]) Is Nothing Then Exit Sub</SPAN></SPAN>
Select Case [b46].Value</SPAN></SPAN>
Case "Close GCIS Client - No Limits in Place": Range("B48:B66").Interior.ColorIndex = 1</SPAN></SPAN>
Case "Existing GCIS Client - No Changes": Range("B48:B66").Interior.ColorIndex = 1</SPAN></SPAN>
Case "Existing GCIS Client - Changes": Range("B47:b66").Interior.ColorIndex = 0</SPAN></SPAN>
Case "New to GCIS": Range("B48:b66").Interior.ColorIndex = 0</SPAN></SPAN>
Case "Delete/Inactive GCIS Client - No Longer Trading": Range("B48:b66").Interior.ColorIndex = 1</SPAN></SPAN>
'Case Else: Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0</SPAN></SPAN>
End Select</SPAN></SPAN>
End Sub</SPAN></SPAN>

 

[VoG]

You need to combine them like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, [b7]) Is Nothing Then
Select Case [b7].Value
Case "Existing GCIS Group - No Changes": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 1
Case "Close GCIS Group": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 1
Case "Existing GCIS Group - Changes": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0
Case "New to GCIS": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0
'Case Else: Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0
End Select
ElseIf Not Intersect(Target, [b46]) Is Nothing Then
Select Case [b46].Value
Case "Close GCIS Client - No Limits in Place": Range("B48:B66").Interior.ColorIndex = 1
Case "Existing GCIS Client - No Changes": Range("B48:B66").Interior.ColorIndex = 1
Case "Existing GCIS Client - Changes": Range("B47:b66").Interior.ColorIndex = 0
Case "New to GCIS": Range("B48:b66").Interior.ColorIndex = 0
Case "Delete/Inactive GCIS Client - No Longer Trading": Range("B48:b66").Interior.ColorIndex = 1
'Case Else: Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0
End Select
End If
End Sub

 

[Norie]

Perhaps.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Not Intersect(Target, [b7]) Is Nothing Then
        Select Case [b7].Value
            Case "Existing GCIS Group - No Changes": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 1
            Case "Close GCIS Group": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 1
            Case "Existing GCIS Group - Changes": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0
            Case "New to GCIS": Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0
                'Case Else: Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0
        End Select
    End If

    If Not Intersect(Target, [b46]) Is Nothing Then
        Select Case [b46].Value
            Case "Close GCIS Client - No Limits in Place": Range("B48:B66").Interior.ColorIndex = 1
            Case "Existing GCIS Client - No Changes": Range("B48:B66").Interior.ColorIndex = 1
            Case "Existing GCIS Client - Changes": Range("B47:b66").Interior.ColorIndex = 0
            Case "New to GCIS": Range("B48:b66").Interior.ColorIndex = 0
            Case "Delete/Inactive GCIS Client - No Longer Trading": Range("B48:b66").Interior.ColorIndex = 1
                'Case Else: Range("B16:B30,B32:B34,B36:B39,J29:J30").Interior.ColorIndex = 0
        End Select
    End If

End Sub