Code using Format Function

[Mike]

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

 

[Frank Kabel]

Hi Mike

if i understood you correctly you would like to change the cell format
based on the value in A1. I've adapted your code to accomplish this

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

x = Worksheets("Tabelle1").Range("A1").Value
If x = "G" Then
Worksheets("Tabelle1").Range("b1").NumberFormat = "0.000%"
Else
Worksheets("Tabelle1").Range("b1").NumberFormat = "$#0.000"
End If
End Sub

HTH
Frank

 

[Bob Phillips]

Mike,

This is what you want

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
With Target
If .Value = "G" Then
.Offset(0, 1).NumberFormat = "0.000%"
ElseIf .Value = "H" Then

.Offset(0, 1).NumberFormat = "$#,##0.000"
End If
End With
End If

ws_exit:

Application.EnableEvents = True
End Sub

This code is triggered by a change to A1, so A1 is Target in that instance,
and the active sheet is the sheet that the worksheet code is in.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)