clear contents of cell

[Curt]

trying to have when an entry in row column (L) is made
clear contents of same row column (P)
have following but doesn't clear as wanted
thanks

Dim lr As Long
lr = Cells(Rows.Count, 200).End(xlUp).row
If Not Intersect(Target, range("L200:L" & lr)) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value >= 10 Then
Sheets("data").range("P" & Target.row).ClearContents
End If
End If

 

[Per Jessen]

Hi

You are trying to calculate 'lr' based on column 200, which I guess is not
what you want.

Dim lr As Long
lr = Cells(Rows.Count, "L").End(xlUp).Row
If Not Intersect(Target, Range("L200:L" & lr)) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value >= 10 Then
Application.EnableEvents = False
Sheets("data").Range("P" & Target.Row).ClearContents
Application.EnableEvents = True
End If
End If

Regards,
Per

 

[Atishoo]

Change your clear contents to .value=""

 

[Atishoo]

You may also want to apply your sub to the last selected cell rather than the
active cell so that it will apply its action to the cell you have just filled.
I use the following to retrieve the address of the previous cell.

Dim LastSelected As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
LastSelected = Target.Address
End Sub

Note that Dim lastselected as string appears above private sub worksheet
selection change