Formatting cells

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

I want to set up a cell to format it so no matter what is
entered by the user is always rounds up or down to the
nearest $100.
 
Formatting cannot change the underlying value in the cell, and
there's no formatting that will display a value rounded to the
nearest hundred.

To change the value, you can use another cell and the ROUND()
function:

=ROUND(A1,-2)

You could use an event macro to round. Put this in the worksheet
code module (right-click on the worksheet tab, choose View Code,
paste the code in the window that opens, then click the XL icon on
the toolbar to return to XL):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
Application.EnableEvents = False
.Value = Application.Round(.Value, -2)
Application.EnableEvents = True
End If
End With
End Sub

(Note: the VBA6 Round() function works differently than XL's round
function for values with a 5 in the least significant digit,
therefore my use of Application.Round())
 
Back
Top