# Formatting decimal places based on text in another cell.

#### mandareds2004

##### New Member
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!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Welcome to MrExcel Board!

How about using a Select Case statment?:

Something like:
Code:
``````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!

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?

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.

Replies
10
Views
637
Replies
1
Views
355
Replies
9
Views
8K
Replies
5
Views
4K
Replies
1
Views
3K

Threads
1,214,437
Messages
6,119,520
Members
448,903
Latest member
StephMJ

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back