Delete Corresponding Cell Using ByVal

[David]

I have two columns of data in C & E. C is for a dollar amount, E is for a
percentage amount. Only ONE of the columns can have data at a time. I'm
trying to set up a ByVal on the sheet so that if data is entered in column C,
the data in the corresponding cell in column E would automatically be deleted
and vice versa. The range right now is C4:C22 and E4:E22, but could be
expanded (I can make that change manually, but it would be nice to have the
ranges be named to so when changes are made, no change to the code is
necessary.
I have the ByVal code with a range, but don't know how to set up the code to
delete the corresponding cell value. I would appreciate help with this.

David

 

[Barb Reinhardt]

Put this in the worksheet change event of the worksheet you are changing.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCRange As Excel.Range
Dim myERange As Excel.Range
Dim myRange As Excel.Range

If Target.Count > 1 Then Exit Sub

Set myCRange = Me.Range("C4:C22")
Set myERange = myCRange.Offset(0, 2)
Set myRange = Union(myCRange, myERange)


If Not Intersect(Target, myRange) Is Nothing Then
Application.EnableEvents = False

If Not Intersect(Target, myCRange) Is Nothing Then
Target.Offset(0, 2).ClearContents
ElseIf Not Intersect(Target, myERange) Is Nothing Then
Target.Offset(0, -2).ClearContents
End If
Application.EnableEvents = True
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRowC As Long, LastRowE As Long
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
LastRowC = Cells(Cells.Rows.Count, "C").End(xlUp).Row
LastRowE = Cells(Cells.Rows.Count, "A").End(xlUp).Row
If Not Intersect(Target, _
Range("C4:C" & LastRowC, "E4:E" & LastRowE)) Is Nothing Then
Application.EnableEvents = False
If Target.Column = 3 Then Target.Offset(, 2).ClearContents
If Target.Column = 5 Then Target.Offset(, -2).ClearContents
Application.EnableEvents = True
End If
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

oops,

an error, try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRowC As Long, LastRowE As Long
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
LastRowC = Cells(Cells.Rows.Count, "C").End(xlUp).Row
LastRowE = Cells(Cells.Rows.Count, "E").End(xlUp).Row
If Not Intersect(Target, _
Range("C4:C" & LastRowC, "E4:E" & LastRowE)) Is Nothing Then
Application.EnableEvents = False
If Target.Column = 3 Then Target.Offset(, 2).ClearContents
If Target.Column = 5 Then Target.Offset(, -2).ClearContents
Application.EnableEvents = True
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Rick Rothstein]

Here is another event code procedure that will do what you asked...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
For Each Cell In Target
If Len(Cell.Value) > 0 Then
If Cell.Column = 3 Then Cell.Offset(0, 2).ClearContents
If Cell.Column = 5 Then Cell.Offset(0, -2).ClearContents
End If
Next
End Sub