Rounding Format

[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.

 

[Peo Sjoblom]

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

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.

 

[SMILE]

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

 

[Peo Sjoblom]

That will only change the display