Event code first...........................
Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module. Alt + q to return to the Excel window. Indent is produced when
you enter text(numerics will not trigger the event) into a cell in any cell in
My_Range. Right or left indent is up to you. Just swap the two lines by
removing or adding an apostrophe.
Private Sub Worksheet_Change(ByVal Target As Range)
Const My_Range As String = "A1:A100" 'adjust to suit your needs
Dim cell As Range
On Error GoTo endit
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(My_Range)) Is Nothing Then
With Target.SpecialCells(xlCellTypeConstants, _
xlTextValues)
Target.Value = Target.Value & " " 'right indent
'OR
'Target.Value = " " & Target.Value 'left indent
End With
End If
endit:
Application.EnableEvents = True
End Sub
Macro code next.....................................
Alt + F11 to open the VBEditor. CTRL = r to open the Project Explorer.
Right-click on your workbook/project and Insert>Module. Copy/paste the macro
below to that module.
Sub add_space()
Dim rngR As Range, rngRR As Range
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
rngR.Value = rngR.Value & " "
'OR
'rngR.Value = " " & rngR.Value
Next
End Sub
Alt + q to return to the Excel window.
This macro can be run on any selected range by going to Tools>Macro>Macros and
"Run". You could assign it to a button or shortcut key.
Gord