Forum Discussion
automatic numeric number change
I am in the process of an old software conversion into excel. How would one format a cell to see a whole number and automatically change it to inches. i.e. 20 typed in would show as 240 in that col...
instead of trying to format the original feet column, I would instead just create a helper column that converts the feet to inches by just doing the feet column * 12. That way you're not changing any source data and have both to reference if needed
on my sample data, i tried that. the programmer asked to only send column data as such, without any added data columns or functions like you suggested, so i figured i would ask the pros. thanks
If you really want to do this:
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Not Intersect(Range("B2:B100"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each rng In Intersect(Range("B2:B100"), Target) If rng.Value <> "" Then If IsNumeric(rng.Value) Then rng.Value = 12 * rng.Value End If End If Next rng Application.EnableEvents = True Application.ScreenUpdating = True End If End SubChange both instances of B2:B100 to the range where you want to multiply numbers with 12.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Warning: undo won't work for entering/editing data in the specified range. Clicking Undo or pressing Ctrl+Z will undo the previous action.
