Solved: Number Formatting

[chem101]

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]

If you go to Format Cell and choose the Percentage formatting, it should do what you want.

[chem101]

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]

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]

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]

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]

Thank you! Works Great!!

[Kenneth Hobs]

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.