View Full Version : Solved: Number Formatting
chem101
12-27-2010, 08:42 AM
Hello Everyone,
I would like to format an empty cell to show a percentage. I would like the user to enter '10' and have 10% display in the cell. Please advise.
Thank you for your assistance!!
mikerickson
12-27-2010, 08:43 AM
If you go to Format Cell and choose the Percentage formatting, it should do what you want.
chem101
12-27-2010, 08:56 AM
I thought so too but when I enter 10 the cell shows 1000.% Any suggestions? I feel there's got to be an easy fix to this.
Thank you for your help!!
Kenneth Hobs
12-27-2010, 09:18 AM
You should probably recheck as it works for me.
Of course 10% is really 0.10 if you reference it in another cell using number format with two decimal places.
chem101
12-27-2010, 09:26 AM
I'm right clicking on the empty cell (no formula or formatting in this cell) and choosing Format Cells. From the Number tab I'm choosing the Percentage option in the Category list. I'm selecting 0 decimal places. When I enter 10 in the cell I'm getting 1000%. What should I be doing differently? Please advise.
Thank you for your assistance!!
Bob Phillips
12-27-2010, 09:33 AM
Maybe
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H5" '<<<< change to suit
On Error GoTo ws_exit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If IsNumeric(.Value) Then
.Value = .Value / 100
.NumberFormat = "0%"
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub
This is worksheet event code.
To implement it, select the sheet tab, right click, and
select View Code.
Paste this code into the code module that opens in the
VBIDE.
Then close the VBIDE and test it in Excel.
chem101
12-27-2010, 09:55 AM
Thank you! Works Great!!
Kenneth Hobs
12-27-2010, 11:44 AM
You lost me on this one. Using the xls code, every time a cell in the intersection range is changed, it will be divided by 100. Try entering 10 again to see what will happen.
Of course if you already have 10 in a cell with General format and the value should have been 0.10 or 10%, then the Percentage format method explained would duplicate your problem. To fix those problems, copy 100 in any cell and then Paste Special and choose Divide. Then format as explained will suffice.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.