Rounding Format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to format a cell, that when the user enters an amount in it, the cell formats it to round to the nearest 5 cent

Can this be done? I know you can do this, if the formaula is in another cell, but I need the result to be in the same cell as the original amount entered.
 
One way, assume the cell is A1 (replace with your cell)
Right click the sheet tab of the sheet where you want this and select
view code and paste in the following

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Target.Value = Application.Round(Target.Value / 0.05, 0) * 0.05
Application.EnableEvents = True
End If
End Sub


--

Regards,

Peo Sjoblom

User said:
I would like to format a cell, that when the user enters an amount in it,
Click to expand...
the cell formats it to round to the nearest 5 cent.
Can this be done? I know you can do this, if the formaula is in another
Click to expand...
cell, but I need the result to be in the same cell as the original amount
entered.
 
Hi
Try
Format|Cells|Number|Enter the decimals 2

toms



I would like to format a cell, that when the user enters an amount i
it, the cell formats it to round to the nearest 5 cent.

Can this be done? I know you can do this, if the formaula is in anothe
cell, but I need the result to be in the same cell as the origina
amount entere
 
Back
Top