2007 Excel Clear Content in Indirect Cells

How do I apply this code (from the original question,which works perfectly on a single selection) to a range of cells? If B2 changes clear C2, if B3 change clear C3 etc

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Address = "$A$1" Then
      [B1] = ""
   End If

End Sub
notpandoraAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Try this:



Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngcell As Range
    On Error GoTo err_handle
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Application.EnableEvents = False
        For Each rngcell In Intersect(Target, Range("B:B"))
            rngcell.Offset(, 1).ClearContents
        Next rngcell
   End If

clean_up:
    Application.EnableEvents = True
    Exit Sub
err_handle:
    MsgBox Err.Description
    Resume clean_up
End Sub

Open in new window

0
 
RunriggerCommented:
Private Sub Worksheet_Change(ByVal Target As Range)

  Activecell.offset(0,1).value = ""

End Sub
0
 
MWGainesJRCommented:
Try this
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.column = 2 Then
     target.offset(0,1).clear
   End If

End Sub

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
MWGainesJRCommented:
runrigger's solution will work on all columns, not just B, not sure if it is intended for all columns....If it is, then runrigger's solution would be more efficient.....
0
 
RunriggerCommented:
I did intend it for all columns (question almost points to this, but that's my interpretation!), otherwise yours is best.
0
 
notpandoraAuthor Commented:
I suspect I didn't add enough detail to the question :(
Column B is a List, Column C is another list (Indirect), For example a user can enter FT to Column B and NORM to Column C (OK) and then change Column B to Cas, Cas and NORM is not an allowable selection and NORM does not appear on the indirect list. if Cas is selected first.

MWGainesJr solution causes some sort of endless loop that crashes Excel
Runrigger solution wipes out the validation list in Column C
I have attached the file ith one line working on the enter sheet
Cheers, fc
cond-lists.xlsm
0
 
notpandoraAuthor Commented:
Worked a treat thanks Rorya
0
 
RunriggerCommented:
this should get around the loop/crash issue!

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 2 Then
        ActiveCell.Offset(0, 1).ClearContents
    End If

End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.