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



In article ,
"pantelis" wrote:

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

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

"JE McGimpsey" wrote in message
...
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



In article ,
"pantelis" wrote:

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

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").

In article ,
"pantelis" wrote:

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.