Validation Default Value

[Lance Fairway]

I'm using a validation list to populate cell B7 on my worksheet. The
choices on this list are dynamic and change based on the value in cell
H6. Cell B6 then shows a VLOOKUP value based on the contents of cell
B7.

The problem is that I often need to change the value of H6 and when I
do so it leaves the previous value in cell B7 until a new choice is
made from the drop-down list.

Is there a way to change the value of cell B7 to be either a blank or
maybe make it default to the first choice on the validation list?

Thanks for your help.

 

[Stopher]

Why would you want to change the value of B7 until a new selection was
made from the drop-down in H6?

Seems a little confusing, but are you having the problem when you
reopen the worksheet, that the last selection from H6 is still giving
the results in B7, even though the list in the dropdown is set to the
default or blank first choice?

 

[Lance Fairway]

I finally found my answer in a post by Frank Kabel. Here's what
worked:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("H6")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value = 0 Then
Range("B7").Value = 1
Else
Range("B7").Value = Range("H6")
End If
End With

CleanUp:
Application.EnableEvents = True
End Sub