Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code using Format Function
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code using Format Function
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code using Format Function
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) "Mike" wrote in message news:5TVLb.14581$na.11873@attbi_s04... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to conditional format all black after date specified in code? | Excel Discussion (Misc queries) | |||
Code Date Format Depending on Computer format | Excel Discussion (Misc queries) | |||
Format Cells by code | Excel Worksheet Functions | |||
date format in code | Excel Discussion (Misc queries) | |||
zip code format | Excel Discussion (Misc queries) |