Beth

Indenting is a crap-shoot at best what with different fonts and resolutions.

Use this macro to indent 5 spaces(not characters). No error-check and if cell
already contains leading spaces, will add to them. Works on both numbers and
text but not cells with formulas.

Sub Format_Indent5()
Dim cell As Range, I As Integer, tempS As String
For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
If Not cell.HasFormula Then
If cell.PrefixCharacter = "'" Then
cell.NumberFormat = " @"

ElseIf IsNumeric(cell.Value) Then
cell.NumberFormat = " " & cell.NumberFormat

Else
cell.NumberFormat = " @"
End If
End If
Next
End Sub


The following allows you to choose number of spaces and indenting left or
right.

Sub Indent_Text()
Dim num As Integer, str As String
Dim cell As Range, I As Integer

num = Application.InputBox(prompt:="Enter the number of " & _
"spaces to indent text. If a negative number " & _
"is entered, text will be shifted left that ." & _
"number Truncation may occur. Only text " & _
"entries are affected.", _
Type:=1)
If num = 0 Then
MsgBox "No value entered. Activity halted."
End
End If
If num 0 Then
For I = 1 To num
str = str & " "
Next
For Each cell In Intersect(Selection, _
ActiveSheet.UsedRange)
If (Not IsEmpty(cell)) And (Not IsNumeric(cell)) And _
(Not Left(cell.Formula, 1) = "=") Then
cell.Value = str & cell.Value
End If
Next cell
Else
For Each cell In Intersect(Selection, _
ActiveSheet.UsedRange)
If (Not IsEmpty(cell)) And (Not IsNumeric(cell)) And _
(Not Left(cell.Formula, 1) = "=") Then
If Len(cell.Value) + num 0 Then
cell.Value = Right(cell.Value, _
Len(cell.Value) + num)
Else
cell.ClearContents
End If
End If
Next cell
End If
End Sub


Gord Dibben Excel MVP With thanks to Bob Flanagan for the code.

On Thu, 3 Mar 2005 07:27:01 -0800, "Beth" <Beth @discussions.microsoft.com
wrote:

We work with standards that have been set for the company. One is that when
creating tables, the indentation of sub-categories of a table must be 5
spaces. The indent option (under Format/Alignment), when set to five, seems
to actually indent 15 spaces. I'm looking for a way to create a template that
we can use as a basis for all tables, is there a way to set the indent option
so that using it actually indents five spaces (as in five space bar spaces)?
Thanks in advance.