Modifying .NumberFormat based on .Value
I need to change the format of a cell to Euro based on its value. If it is between 1 and -1, then it will have three decimal places. otherwise he will only have one. It is also necessary that the euro symbol must come before the number. I am having problems with the euro sign appearing and zeros appearing after the decimal for numbers like 23, which I would like to show as 23.0 or 0.9, which should change to 0.900.
Try it (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
Use conditional criteria in a complex custom format.
Selection.NumberFormat = "[>1]€ 0.0;[<-1]€ -0.0;€ 0.000"
1) Highlight the cells (cells) you want to format. For this example, I am assuming it is 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 format settings go to number, select custom and enter your format. Enter "ALT + 0128" followed by "0.000". "ALT + 0128" is the ASCII code for the euro symbol
5) Click OK to return to the worksheet. If the cell is still selected, change the formatting of the number to custom, using the same formatting type as above; "ALT + 0128" and "0.0".
6) Done!
Custom format:
[<-1]$#,##0.0;[<1]$#,##0.000;$#,##0.0
Change the currency symbol in the International Options. - Excel will use whatever is installed in the control panel