Clear contents of cell based on specific option chosen in drop-down

[RachVG]

Hi,

I have a scenario where I have a dropdown box in column K (using data validation) giving options of different types of unit that are available to hire, in column M there is a list of hire periods (1-2 days, a week, a month etc). I have a formula in column N that looks at these cells and depending which options are chosen, performs a vlookup on different price tables and populates the cell in N with the hire cost.

However we have one final option on the drop down in K, for misc costs. Sometimes there are delivery charges etc which are priced ad-hoc.

I would like a piece of code that will clear the contents of cell N, if "misc" is chosen in K (column M becomes irrelevant here), so that the user can just input the price.

My formula in N uses IF statements so if the Misc option is chosen the cell still appears blank and we can just type over the top, but I would ideally like to have it physically clear the contents. I know some of the users of the sheet are likely to double click into the cell out of habit when pasting (to make sure none of the formatting is copied as we are pasting the prices from a website) and that clicks them into the formula and confuses things as it's a long formula.

I have looked around but I can only find options to clear a cell whenever the dropdown is changed, but I don't want to do that - just when this specific option is chosen. I can't quite get my head around how to adapt these pieces of code to do what I want.

 

[SteveO59L]

can you not add to you existing if statement in n?

clearing the n cell would remove the formula

something like =if(k?="misc","",if(..........

 

[RachVG]

Thanks, maybe I didn't explain it quite right - if K = Misc, then I do want N to be cleared and I do want the formula to be removed. I want an empty cell for the user to manually input whatever price has been quoted on this row only, rather than it performing the lookup - as this price won't be found on the lookup tables. As I mentioned I know we could just type the price over the top, but I want to make it foolproof for my users and I know they will end up confused by the formula instead of just typing over it on these few occasions!

Eg below, when K5 is Misc, I would like this to trigger completely clearing cell N5 leaving an empty cell for the user to type in "£256.77" etc.

 

[SteveO59L]

as a worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = "5" And Target.Column = "11" And Target.Value = "misc" Then
Target.Offset(0, 3).ClearContents
End If
End Sub