I need to apply formatting to a cell, based upon a value in another cell.
A1 = G
B1 = .1

If the value in A1 = "G", then the formatting in B1 should result in 10.000%
If the value in A1 = "H", the the formatting in B1 should result in $0.100

But with my code below, when A1 = "G", B1 appears in sheet1 as 10.00% or
when A1 = "H", B1 appears in sheet1 as $0.10. It does not want to increase
the decimal places. How do I get the code to increase decimal places?

Thanks Mike


Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As String
Dim y As Single
Dim MyStr As String

x = Worksheets("Sheet1").Range("A1").Value
y = Worksheets("Sheet1").Range("B1").Value

If x = "G" Then
MyStr = Format(y, "0.000%")
Else
MyStr = Format(y, "$#0.000")
End If
Worksheets("Sheet1").Range("b1").Value = MyStr

End Sub