MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Custom Currency Format

  Asked By: Oscar    Date: Feb 18    Category: MS Office    Views: 748

I want to format the excel cells to get the below result:
Value = 10.5 Then appear as 10.5
Value = 10.0 Then appear as 10 (no decimal)
Value = 0 Then appear as 0
Value = 10000 Then appear as 10,000 (no decimal)

I have been trying hard to build One a custom format to cover the above
condition - but so far i havent been as successful.
Any advise/help would be helpful.



2 Answers Found

Answer #1    Answered By: Dixie Jacobs     Answered On: Feb 18

Right click on your worksheet tab and view code.
Add the following code to the worksheet VBA editor.
The code runs everytime a value is column B changes, (change to your column)
Formats the cell based on your critria.

Private Sub WorkSheet_Change(ByVal Target As Range)
If Cells(Target.Row, Target.Column) = 0 Then Cells(Target.Row,
Target.Column).NumberFormat = "0"
If Cells(Target.Row, Target.Column) = Empty Then Exit Sub
If Target.Column = 2 Then 'column B
If Cells(Target.Row, Target.Column) > 999 Then 'Add commas to greater
than 999
Cells(Target.Row, Target.Column).NumberFormat = "#,###"
ElseIf Cells(Target.Row, Target.Column) = Int(Cells(Target.Row,
Target.Column)) Then '10.0
Cells(Target.Row, Target.Column).NumberFormat = "0"
Else '10.5
Cells(Target.Row, Target.Column).NumberFormat = "0.0"
End If
End If
End Sub

Answer #2    Answered By: Saiqa Mian     Answered On: Feb 18

No, I don't think custom  format can handle all this.

Didn't find what you were looking for? Find more on Custom Currency Format Or get search suggestion and latest updates.