Entering a formula depending on criteria of cell

Thank you fro that bit of code, I followed your instructions, but have code already on that sheet. So i tried to fit it in the code already there and its not working, I dunno if its the way i have entered i.e. in wrong order or where the formula is not working cause its an Array..

i.e. has {} back and front, as its was original enetred in as a array into the cell.

Code for worksheet as it is at the moment

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range

If Intersect(Target, Range("F2:f3000,g2:g3000,h2:I3000,l2:l3000,m2:m3000,n2:n3000,o2:o3000")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Application.WorksheetFunction.IsText(Target.Value) Then
Target.Value = UCase(Target.Value)

 For Each cell In Target
        If Not Intersect(cell, Range("A2:A10000")) Is Nothing Then _
            Cells(cell.Row, "E") = "{=MAX(IF(ISNUMBER(0+MID(D13,1,ROW($1:$4))),0+MID(D13,1,ROW($1:$4))))+IF(ISNUMBER(MATCH(RIGHT(D13,1),$X$2:$X$27,0)),VLOOKUP(RIGHT(D13,1),$X$2:$Y$27,2,0)/10000,0)}"
    Next cell
    
End If
Application.EnableEvents = True

End Sub

Thanks

G