Format Indent based on a value in a cell

[gheyman]

Can you write code that will indent a cell based on a number in another cell?

Column A will have a number in it between 1 and 7. Based on the number in that cell can I indent the text in Column C using VBA code?

 

[Ron Coderre]

This exact situation was just asked in another forum.
See if the posts there are helpful:
http://social.answers.microsoft.com...e/thread/a008f056-7ccb-44ac-8f06-deba2f294cf4

 

[gheyman]

Thanks, but I not sure thats what I am looking for. I was thinking of putting it in VBA code so that the sheet formated each time it was activated or maybe a button.

 

[shg]

You can do this with a user-defined function.

If the indent level is in A1, and the cell to indent is B1, then in C1 (or any convenient column),

=SetIndent(B1,A1)

Function SetIndent(r As Range, ByVal Level As Long) As Variant
    ' shg 2008
    ' Sets the indent level of r from 0 to 15 and returns the indent level
 
    Dim cell As Range
 
    SetIndent = IIf(Level < 0, "Min is 0!", IIf(Level > 15, "Max is 15!", Level))
    If Level < 0 Then Level = 0 Else If Level > 15 Then Level = 15
 
    For Each cell In r
        With cell
            If Level - .IndentLevel Then .InsertIndent Level - .IndentLevel
        End With
    Next cell
End Function

 

[Ron Coderre]

Thanks for the clarification.