Data Validation - Worksheet change VBA

[bravia]

Hi there,

I'm very new to the VBA, and have a reasonable Excel knowledge of formulas etc.

I have a sheet that I use an Array Formula to give me 4 options which is referenced in a range by a data validation drop down list (each row has it's own range & result.).

The Vlookup is based on the value in col D, (searches on sheet 'Fixture Library' and result is produced in cols N-Q, there will only be a maximum of 4 results. The data validation references the N-Q range for each row. This all works well.

The next step is what I'm struggling with - I have found a great VBA script online that on clicking on the data validation cell will refresh the result to the first. I would like instead of clicking on the cell with the validation that anytime there is a change in col D, it refreshes it's result in col F. Is this possible can anyone help with the script?

Example file is here: Dan Young sent you 1 item. - A number of cols are hidden/deleted to prevent confusion.

Script I am currently using:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20160725
    Dim xFormula As String
    On Error GoTo Out:
    xFormula = Target.Cells(1).Validation.Formula1
    If Left(xFormula, 1) = "=" Then
        Target.Cells(1) = Range(Mid(xFormula, 1)).Cells(1).Value
    End If
Out:
End Sub

••••ˇˇˇˇ

 

[QuietRiot]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xFormula As String
    
    ' if unit # changes
    If Target.Column = 1 Then
        xFormula = Range("F" & Target.Row).Validation.Formula1
        Range("F" & Target.Row).Value = Range(Mid(xFormula, 1)).Cells(1)
    End If
    
End Sub

 

[bravia]

Thanks! That works perfectly! Since this is working it's brought up another little tweak I could do with some help with.... If a cell in col A is empty it doesn't update col F with an empty value, is it possible to include that in the script?