Clear cell contents based on selected option

[seldom excel]

Hello,

I am an excel novice, and need help with my Job Tracking sheet.

Basically what I want to happen, is when any of the "L" cells are changed to "Yes", the contents of cells "B" through to "L" are cleared... I have it set so that "Yes" is selected from a drop down menu and not manually entered.

I am hoping this is possible and not too difficult... I hope I have explained myself well enough, and thank you in advance for any help offered.

 

[Fluff]

Hi & welcome to MrExcel.
Which rows should this work on?

 

[vcoolio]

Hello SE,

Perhaps this placed into the "Job Tracking" sheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim rng As Range: Set rng = Range("L6", Range("L" & Rows.Count).End(xlUp)) 'Change L6 to the first cell below the "Completed" heading.
        
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        
        x = Target.Row

        If Target.Value = "Yes" Then
              Range(Cells(x, "B"), Cells(x, "L")).ClearContents
        End If

End Sub

To implement the code:-

- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Selecting "Yes" from the drop down will immediately trigger the code.

I hope that this helps.

Cheerio,
vcoolio.

 

[seldom excel]

Hello SE,

Perhaps this placed into the "Job Tracking" sheet module:-

Private Sub Worksheet_Change(ByVal Target As Range)

        Dim rng As Range: Set rng = Range("L6", Range("L" & Rows.Count).End(xlUp)) 'Change L6 to the first cell below the "Completed" heading.
       
        If Intersect(Target, rng) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
       
        x = Target.Row

        If Target.Value = "Yes" Then
              Range(Cells(x, "B"), Cells(x, "L")).ClearContents
        End If

End Sub

To implement the code:-

- Right click on the sheet tab.
- Select "View Code" from the menu that appears.
- In the big white code field that then appears, paste the above code.

Selecting "Yes" from the drop down will immediately trigger the code.

I hope that this helps.

Cheerio,
vcoolio.

Thanks so much! This works perfectly!

 

[vcoolio]

You're welcome SE. I'm glad to have been able to assist and thanks for the feed-back.

Cheerio,
vcoolio.