Clear cell contents VBA code - 1 row at a time....

[cabellos]

Hi,

I would really appreciate some assistance with the VBA code to clear the text from a cell if the cell text is updated to 'Closed'.

The two columns in question are K and M. When K8 is updated to 'Closed' (via drop down box) I want to clear the text in M8.

I want this coding to support this for each additional row in the same two columns (rows 8 : 100). E.g K9 & M9, K10 & M10 etc.

Thanks in advance for anyone who can support.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make selection in column K.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 11 Then Exit Sub
    If Target = "Closed" Then
        Target.Offset(, 2).ClearContents
    End If
    Application.ScreenUpdating = False
End Sub

 

[cabellos]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make selection in column K.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 11 Then Exit Sub
    If Target = "Closed" Then
        Target.Offset(, 2).ClearContents
    End If
    Application.ScreenUpdating = False
End Sub

Hi

 

[mumps]

Hello. Have you tried the macro?

 

[cabellos]

Hi,

Many thanks for your swift reply. I really appreciate the support.

I did in fact try the macro and it worked. However, the cell it cleared included IF statements and unfortunately it also cleared this formula.

Not one to give up, I took another look at my IF statements and have amended these slightly to achieve what I wanted.

Many thanks for your support anyway as this code may still be useful.

 

[mumps]

Does column M contain formulae?