asked on

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

Runrigger

Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub

MWGainesJR

Try this
Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub

MWGainesJR

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.....

Runrigger

I did intend it for all columns (question almost points to this, but that's my interpretation!), otherwise yours is best.

notpandora

ASKER

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

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

notpandora

ASKER

Worked a treat thanks Rorya

Runrigger

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