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