|You can't do it with a formula but you can do it with a WorksheetChange Macro.|
First, you need to know that you cannot Protect specific cells, you can only Protect an entire sheet.
However, you can Lock and Unlock specific cells so that once you Protect the sheet the Locked cells can not be changed.
To accomplish what you want try this:
1 - Select the entire sheet by clicking in the box above the Row numbers.
2 - Use Format...Cells...Protection tab
3 - Uncheck the Locked box. Click OK
4 - Select the cells that you want to Protect
5 - Use Format...Cells...Protection tab
6 - Check the Locked box. Click OK
Now, when you Protect the sheet, only the cells that you have Locked will be Protected.
7 - Right Click the sheet tab and choose View Code
8 - Paste in the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Assumes your Drop Down is in B1
If Target.Address = "$B$1" Then
'Protect the sheet if B1 is "yes"
If Target = "yes" Then ActiveSheet.Protect Else _
'Unprotect the sheet if B1 is "no"
If Target = "no" Then ActiveSheet.Unprotect
Note: Unless you also Protect the code, any user could delete it and defeat what you are trying to do.
Note (the sequel): When the sheet is Unprotected the user could Unlock the cells and the Protection would have no effect next time you put "no" in B1.