Solved: Automating Scipt

[Opv]

I found the following snippet and am trying to adopt it for my purposes. What I want to do is use the trigger cell (c2) to automatically clear the target range once the value of C2 is changed to "Yes" and then to automatically change the trigger cell back to "No."

I thought from the name of the Sub that it would place some sort of button for me to press to execute the code without having to go through so many different steps. On after thought, if there is a way to automate the entire process, as described above, that would be preferable.

[VBA]
Sub CommandButton_Click()
If [c2] = "Yes" Then
[C4:C500].ClearContents
End If
End Sub
[/VBA]

Thanks,

Opv

[Bob Phillips]

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "C2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

If .Value = "Yes" Then

Me.Range("C4:C500").ClearContents
.Value = "No"
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/vba]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[mbarron]

Right click on the Tab for the sheet you'd like to run the routine.
Paste the following:
[VBA]Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C2")) Is Nothing Then
If Target = "Yes" Then
Range("C4:C500").ClearContents
Target = "No"
End If
End If
End Sub[/VBA]

[Opv]

Thanks to both of you. Both scripts work like a charm.

Opv