CODEWITHSUNDEEP
vbaexcel
AmandaNicole
AmandaNicole

Reputation: 33

Clear cell contents when previous cell is changed

I am not familiar with VBA, and usually Google to try and find solutions, so bear with me.

What I want to achieve is: a cell in column M is cleared when the cell in column L (in the same row) is changed. Right now I have the below code: 

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "L3" Then Range("M3").ClearContents
End Sub

Which applies great when just using L3 & M3, but I can't figure out how to get it to apply to the entire column (or technically I need it to work for L3:L4000 and M3:M4000). Any thoughts? Thanks in advance!

Upvotes: 0

Views: 1641

Answers (3)

Slai
Slai

Reputation: 22876

To make it a tiny bit more responsive/faster, you can use the .Column and .Row properties: 

If Target.Column = 12 And Target.Row >= 3 Then Target(, 2).ClearContents

Upvotes: 0

user3598756
user3598756

Reputation: 29421

sticking to your code as much as possible: 

Private Sub Worksheet_Change(ByVal Target As Range)
    If Left(Target.Address(, False), 1) = "L" Then Target.Offset(, 1).ClearContents
End Sub

Upvotes: 0

nightcrawler23
nightcrawler23

Reputation: 2066

You just need to get the Target's row and clear same row in Column M

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("L:L")) Is Nothing Then
        Range("M" & Target.Row).ClearContents
    End If
End Sub

Upvotes: 2

Related Questions