How to run macro when certain cell deleted?

[radian89]

Hi,

i'm stuck with the macro, to run when certain cell deleted.
here's the case, user will only fill the yellow area, then column F will have values too.

i want to when i press delete/backspace on C6, it'll delete F6 too. but it won't delete, if i only edit the content of C6. so certainly this is not Worksheet change event i guess.

thanks for the helps

adrian

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6")) Is Nothing Then
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Target.Offset(0, 3).Value = ""
End If
End Sub

 

[My Aswer Is This]

Just to be clear I believe you are deleting the contents in the cell not deleting the cell like you said.

Deleting a cell and deleting the contents of a cell are two different things.

 

[JackDanIce]

This will work for cell in column C, try:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x as Long
x = cells(rows.count, 3).end(xlup).row
If Target.cells.count = 1 and not intersect(target, cells(1,3).resize(x)) is nothing and target.value = "" then Target.offset(,3).value = ""

End Sub

If it's a specific range, please state what it needs to be.

 

[My Aswer Is This]

If you want the script to work with all cell in column "C" try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 3 And Target.Value = "" Then Target.Offset(0, 3).Value = ""
End Sub

 

[radian89]

Hi,

sorry for late reply,

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C6")) Is Nothing Then
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Target.Offset(0, 3).Value = ""
End If
End Sub

This will work for cell in column C, try:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x as Long
x = cells(rows.count, 3).end(xlup).row
If Target.cells.count = 1 and not intersect(target, cells(1,3).resize(x)) is nothing and target.value = "" then Target.offset(,3).value = ""

End Sub

If it's a specific range, please state what it needs to be.

thanks for the idea... sorry, when i mean delete >> clearcontents, not delete the cells entirely.
and i've modified it to match my conditions, here's my code..

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sh1 As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False


    If Not Intersect(ActiveCell, Range("C5:C11")) Is Nothing Then


        If Target.value = vbNullString Then
            Set sh1 = Sheets("Hal Depan")
            
            With sh1
                .Range(.Cells(.Range("PasteRowKe").value, "F")).ClearContents
            End With
        
        End If
    End If
Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

range "Pasterowke", is a dynamic value, based on worksheet selection change, that display on which row, the current selected cells now on.

 

[JackDanIce]

Glad it's resolved