setting fixed decimals in only one column of worksheet

G

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

Toppers

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top