Entering a formula depending on criteria of cell

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False

    For Each cell In Target
        If Not Intersect(cell, Range("F2:F3000,G2:G3000,G2:I3000,L2:O3000")) Is Nothing Then
            If Application.WorksheetFunction.IsText(cell.Value) Then _
                cell.Value = UCase(cell.Value)
        ElseIf Not Intersect(cell, Range("D2:D10000")) Is Nothing Then _
            Cells(cell.Row, "E").FormulaArray = _
                "=MAX(IF(ISNUMBER(0+MID(RC[-1],1,ROW(R1:R4))),0+MID(RC4,1,ROW(R1:R4))))+IF(ISNUMBER(MATCH(RIGHT(RC[-1],1),R2C24:R27C24,0)),VLOOKUP(RIGHT(RC[-1],1),R2C24:R27C25,2,0)/10000,0)"
        End If
    Next cell
    
Application.EnableEvents = True
End Sub