Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Formating Cells
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Formating Cells
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--- -- RyGuy "JohnWFUBMC" wrote: 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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Custom Formating Cells
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 On Fri, 14 Dec 2007 08:36:08 -0800, JohnWFUBMC wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Custom Formating when Excel sheet is Lock. | Excel Discussion (Misc queries) | |||
Custom formating of cells | Excel Worksheet Functions | |||
custom cell formating | Excel Worksheet Functions | |||
custom formating a cell | Excel Worksheet Functions | |||
Custom formating won't work | Excel Worksheet Functions |