Macro to clear certain range if a certain cell has a particular value

[ardykav]

Hi All,

I am looking to run a macro that clears a range of cells if one particular cell has the word "Leftout" in it. I have the below code but not sure how to run it properly as when i prompt to run it asks for a macro name, obviously I am missing something here.
So when Cell F20 has the word leftout in it i want to run the macro so it will then clear cells C19 to C29


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("F20")) Is Nothing Then
If UCase(Range("F20")) = "Leftout" Then
Application.EnableEvents = False
Range("C19:C29").ClearContents
Application.EnableEvents = True
End If
End If
End Sub

thanks in advance

 

[Akuini]

I think it should be:

If UCase(Range("F20")) =[COLOR=#000080] "LEFTOUT"[/COLOR] Then

You can't run it manually.
It is an event driven Sub, so it will run automatically every time you change cell F20 value.

 

[ardykav]

Ok so I more need it for the end of each week to clear a document, I dont want it to run everytime the cell in question is populated. Is there any way I can attach a button to this so it works whenever I press it?

thanks

 

[Akuini]

Ok so I more need it for the end of each week to clear a document, I dont want it to run everytime the cell in question is populated. Is there any way I can attach a button to this so it works whenever I press it?

thanks

Ok
1. In VBE create/insert a module
2. Put the macro 'toClear' in the module
3. create a button in the sheet in question
4. assign the macro to the button

Sub toClear()
If UCase(Range("F20")) = "LEFTOUT" Then
Range("C19:C29").ClearContents
End If
End Sub

 

[ardykav]

thanks, i played around for it a bit and looks like this is the simplest way and it works, thanks for all of your assistance

Sub Clearcontents()

If Range("F20") = "Leftout" Then
Range("C19:C19").Clearcontents

End If
End Sub

 

[Akuini]

thanks, i played around for it a bit and looks like this is the simplest way and it works, thanks for all of your assistance

Sub Clearcontents()

If Range("F20") = "Leftout" Then
Range("C19:C19").Clearcontents

End If
End Sub

You're welcome & thanks for the feedback.