CODEWITHSUNDEEP
excelvbaformattingformat
Evan
Evan

Reputation: 33

Changing .NumberFormat based on .Value

I have to change the format of a cell to Euros based on its value. If it is between 1 and -1 then it will have three decimal places. else, it will have only one. Also the Euro symbol needs to come before the number. I'm having trouble getting the euro sign to show up and having zeros after the decimal show up for numbers such as 23, which I would like to appear as 23.0 or .9 which should change to .900.

Upvotes: 3

Views: 2042

Answers (4)

SeanC
SeanC

Reputation: 15923

Custom format:

[<-1]$#,##0.0;[<1]$#,##0.000;$#,##0.0

Change the currency symbol in the International Options.- Excel will use whatever is set in the control panel

Upvotes: 0

314UnreadEmails
314UnreadEmails

Reputation: 321

1) Highlight the cell(s) that you want formatted. For this example I'll assume its just [A1].

2) Go to conditional formatting > new rule > use a formula to determine which cells to format.

3) Enter =AND(A1<=1,A1>=-1) in the formula bar.

4) In the format settings, go to number, select custom and enter your format. Type "ALT + 0128" then "0.000". The "ALT + 0128" is the ASCII code for the euro symbol

see example here

5) Click okay to return to the worksheet. With the cell(s) still highlighted, change the number formatting to custom using the same type of formatting as above; "ALT + 0128" and "0.0".

6) Done!

Upvotes: 0

user4039065
user4039065

Reputation:

Use the conditional criteria in a complex custom number format.

Selection.NumberFormat = "[>1]€ 0.0;[<-1]€ -0.0;€ 0.000"

Upvotes: 0

TMH8885
TMH8885

Reputation: 888

Try (replacing CellRow and CellColumn with the desired row and column for the cell):

If Cells(CellRow,CellColumn).Value  >= -1 and Cells(CellRow,CellColumn).Value <= 1 then
    Cells(CellRow,CellColumn).NumberFormat = "[$€-2] #,##0.000"
Else
    Cells(CellRow,CellColumn).NumberFormat = "[$€-2] #,##0.0"
End If

Upvotes: 1

Related Questions