format cells: divide by 100

V

vhson

I need to a Custom format for 3 cells in a worksheet. I want to enter
value in any of those cells and have the value divided by 100. Fo
example, I want to enter a 3 digit number like 427 and have its valu
changed to 4.27.

I have looked at Custom formatting and can't figure out how to do it.

I know I can go to Tools/Options/Edit tab and check the Fixed decima
box. That changes the whole worksheet. Can I do that for just 3 cells?

Is there any way of doing this?

Thanks for helping
 
J

JE McGimpsey

Formats can't change the value that's stored in the cells.

One way:

Put this in the worksheet code module (right-click the worksheet code
module and choose View Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, _
Range("A1,D4,J10")) Is Nothing Then
Application.EnableEvents = False
.Value = .Value / 100
Application.EnableEvents = True
End If
End If
End With
End Sub

Change your cell references to suit.
 
P

Peo Sjoblom

Only by using an event macro, do you want to go that way?

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
V

vhson

Thank you to everyone for all the help.

I'm glad to know what doesn't work.

I am going to use an event macro to divide that cell by 100.

Thanks again,

Mik
 
Top