VBA clear rows from a reference cell

[Russk68]

Hello all,
I need to clear a range of cells based on a reference cell.

O5 would be the reference cell. O5 will have a dropdown list ("Clear" & "Select") and when "Clear" is selected, A pop up message: "Are you sure?"
Yes, A5,C5:N5 contents cleared. (B5 is a hidden helper column)
C5,E5,G5,I5,K5,M5= "Spare" (Each cell prints "Spare")
O5 goes back to "Select"
Select C5
End.

I would like the macro to work the same when O9 is the dropdown list ("Clear") and all above applies to row 9.

So, anywhere "Clear" is selected in column O, the same row will show the results of the macro.

Any help is greatly appreciated!

Russ

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Column = 15 Then Exit Sub
   If Target.Value = "Clear" Then
      Application.EnableEvents = False
      Intersect(Target.EntireRow, Range("A:A,C:N")).ClearContents
      Intersect(Target.EntireRow, Range("C:C,E:E,G:G,I:I,K:K,M:M")).Value = "Spare"
      Target.Value = "Select"
      Application.EnableEvents = True
   End If
End Sub

This will work for all cells in col O.

 

[Russk68]

Thank you very much! I appreciate your time on this!

 

[Fluff]

You're welcome