Custom Formating Cells

  • Thread starter Thread starter JohnWFUBMC
  • Start date Start date
J

JohnWFUBMC

I want to format a cell so that when I type a number into that cell it will
take the absolute value of that number and then multiple it by 1000. Is
there anyway to do this without generating a formula in a new cell?
 
This is called 'event code'. Copy the code, right-click the tab, and paste
it into the window that opens up:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
If IsNumeric(Target) Then
Application.EnableEvents = False
Target.Value = Abs(Target.Value * 1000)
Application.EnableEvents = True
End If
End If
End Sub

Notice, the range is A1:A10; change to suit your needs...

Regards,
Ryan---
 
You can't "format" a cell to multiply itself by 1000

You could use event code to do this.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:A10"
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each cell In Target
If cell.Value <> "" Then
cell.Value = cell.Value * 1000
cell.NumberFormat = "###0.00"
End If
Next cell
End If
ws_exit:
Application.EnableEvents = True
End Sub

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

Adjust range and numberformat to suit.


Gord Dibben MS Excel MVP
 
Back
Top