Errors on If Statements

You're missing an "End With", but the With statements don't contribute anything here, Chemist.

Maybe just

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "A5" Then
        Select Case Target.Value
            Case "$/square foot"
                Range("E2").NumberFormat = "$#,##0.00"
            Case "%/construction cost"
                Range("E2").NumberFormat = "0.0%"
        End Select
    End If
End Sub