setting fixed decimals in only one column of worksheet

[gered]

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.

 

[Toppers]

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

 

[Gord Dibben]

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