Private sub macro looping :(

[cmefly]

Hi,

i have the following macro and i'm unsure how to make it work properly:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("c35").Value
Case "X"
Range("C37").ClearContents
End Select

Select Case Range("c37").Value
Case "X"
Range("C35").ClearContents
Exit Sub
End Select

End Sub

c35 and c37 are drop-down menus that contain either "X" or Blank. So when selecting X in either one, the other should result to Blank - but then this activates the first one and so only the first part really works....

any suggestions??

thanks,

Marc

 

[Norie]

Marc

Why 2 select case statements?

I think you might want something like this.

Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Address
        Case "$C$35"
            If Target.Value = "X" Then Target.Offset(2).ClearContents
        Case "$C$37"
            If Target.Value = "X" Then Target.Offset(-2).ClearContents
    End Select
End Sub

 

[wigi]

A possibility could be:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const sCell1 As String = "$C$35"
    Const sCell2 As String = "$C$37"
    
    If Target.Address = sCell1 Then
        If Target.Value = "X" Then
            Range(sCell2).Value = ""
        End If
    ElseIf Target.Address = sCell2 Then
        If Target.Value = "X" Then
            Range(sCell1).Value = ""
        End If
    End If

End Sub

Can you please use

 tags around your VBA code here? Thanks.

 

[cmefly]

awesome! thank you so much!