i want to set 1-2 column's in worksheet to fixed decimals. The only way i
have found is tools-options-edit and set decimal. this changes all future
data for the sheet to be set in this manner. I want to enter quanity x price
= cost. i want quanity without decimals and the other column's with fixed
decimals.

Select 1st column, Format= Cells=Number with zero decimal places
Repeat for second / third (?) column with 2 decimal places

"gered" wrote:

i want to set 1-2 column's in worksheet to fixed decimals. The only way i
have found is tools-options-edit and set decimal. this changes all future
data for the sheet to be set in this manner. I want to enter quanity x price
= cost. i want quanity without decimals and the other column's with fixed
decimals.

gered

Fixed Decimals is a global setting.

You can use Event code to divide your numbers as you enter them in certain
columns.

The code below is written for Columns A and B

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Me.Range("A:B")) Is Nothing Then Exit Sub
If Not IsNumeric(Target.Value) Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
With Target
.Value = .Value / 100
.NumberFormat = "0.00"
End With
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module.


Gord Dibben MS Excel MVP

On Tue, 20 Mar 2007 09:35:29 -0700, gered
wrote:

i want to set 1-2 column's in worksheet to fixed decimals. The only way i
have found is tools-options-edit and set decimal. this changes all future
data for the sheet to be set in this manner. I want to enter quanity x price
= cost. i want quanity without decimals and the other column's with fixed
decimals.