Formatting decimal places based on text in another cell.

[mandareds2004]

I am trying to modify the decimal places based on the text of another cell:

Cell A140 can read either:
Line 1 - "Price to Achieve Financial Goals ($/kWh):"
Line 2 - "Price to Achieve Financial Goals ($/million Btu):"
Line 3 - "Price to Achieve Financial Goals ($/gallon):"

Cell E140 reads different cells based on the correct units:
Cell 'INP-OUT'!$G$51 is in E140 if A140 is Line 1
Cell 'INP-OUT'!$G$50 is in E140 if A140 is Line 2
Cell 'INP-OUT'!$G$49 is in E140 if A140 is Line 3

If the contents of cell A140 equals line 1, I want the result of the equation in cell E140 to show 4 decimal places, with precision: (e.g. 0.3520)

If the contents of cell A140 equals line 2, I want the equation in cell E140 to be 2 decimal places and comma separated with precision: (e.g. 1,000.26)

If the contents of cell A140 equals line 1, I want the equation in cell E140 to be 3 decimal places with precision: (e.g. 0.210)

I have tried a Macro I saw also on this site (see below) but I can't get the commas to work. I am open to a different approach as well if someone suggests it.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim fmt As String
If Target.Address(0, 0) = "A140" Then
If Target.Value = "Price to Achieve Financial Goals ($/kWh):" Then
fmt = "0.0000"
End If
If Target.Value = "Price to Achieve Financial Goals ($/million Btu):" Then
fmt = "#,##0.00"
Else
fmt = "0.000"
End If
Range("E140").NumberFormat = fmt
End If
End Sub

Thanks!

 

[Tazguy37]

Welcome to MrExcel Board!

How about using a Select Case statment?:

Something like:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.Intersect(Target, Range("A140")) Is Nothing Then Exit Sub

    Select Case Target.Value
        Case "Price to Achieve Financial Goals ($/kWh):":
            Target.Offset(, 4).NumberFormat = "0.0000"
        Case "Price to Achieve Financial Goals ($/million Btu):"
            Target.Offset(, 4).NumberFormat = "#,##0.00"
        Case "Price to Achieve Financial Goals ($/gallon):"
            Target.Offset(, 4).NumberFormat = "0.000"
    End Select

End Sub

Hope that helps!

 

[mandareds2004]

Formatting Decimal Points based on text of another cell

Hi thanks for the reply and welcome!

Your Case statement seems to work fine for the decimal places but no comma formatting seems to occur. instead i get a number formated like:

1348.0

I am using Excel 2002, is the "##,#.0" the correct format for this version?

 

[Tazguy37]

They both work for me in XP and 2003. I'd suggest using the macro recorder to record formatting a cell exactly how you want it, and use that format in your code.