Format a cell

  • Thread starter Thread starter pantelis
  • Start date Start date
P

pantelis

Hi all,

Got a questions which i have a problem with.

How do I display 0.0025 as 2.5 per thousand in excel.

i.e. I type in 0.0025 and the cell shows 2.5 per thousand.

Will appreciate anybody's help.

Thanks
Pantelis
 
AFAIK, you can't do that with formatting. You could use a
worksheet_Change macro. Put this in the worksheet code module
(right-click on the worksheet tab and choose view code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("A1"), .Cells) Is Nothing Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Value = Format(.Value * 1000, "0.0") & _
" per thousand"
Application.EnableEvents = True
End If
End If
End With
End Sub
 
Thanks for the reply,

I have done what you said but I cant get it to work for some reason. I
simply copy this code into the sheets VBA but nothing happens.

Could you please help
Thanks
Pantelis
 
Make sure you put the code in a *worksheet* code module, not a regular
code module (as I told you before), and that you adjust the range for
your target (I used "A1").
 
Back
Top