Cell Number Format

[chris]

Is there a way to format a cell's number from 1.2 to 1,200,000? I
know how to format millions to ones but how do I convert from ones to
millions? (I don't want to have to multiply in another cell).

Thanks!!!

 

[AltaEgo]

I could be wrong but, suspect there is no way to achieve what you want using
format.

You could use the sheet change event to multiply numbers entered by one
million and format the result as you wish using code below. Just right-click
on the tab of the appropriate worksheet, click view code and paste. Note.
this will multiply value in column C only. You will need to modify to make
it work on the range you wish.


--
Steve


' *******************************************

Private Sub Worksheet_Change(ByVal Target As Range)



On Error GoTo Worksheet_Change_Error

Set isect = Application.Intersect(Target, Range("C:C"))
' C:C refers to column C
' change this to reference a different range
If isect Is Nothing Then
'do nothing
Exit Sub
Else
If Target.Value < 100000 Then
Target.Value = Target.Value * 1000000
End If

End If


On Error GoTo 0
Exit Sub

Worksheet_Change_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
Worksheet_Change of VBA Document Sheet1"

End Sub

' *******************************************