automatic numeric number change

[RikkeJ]

  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 column?

[#2]

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

[#3]

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

[#4]

@RikkeJ 

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 Sub

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