Indenting Text

  • Thread starter Thread starter Tony Woods
  • Start date Start date
T

Tony Woods

I want to indent text in a range of cells based on the
values contained in another range of cells. Con ditional
formatting does not apply to indenting so how can I do it?
 
I apply data|autofilter, show only those cells that match the condition I want.
then I select that other column (that actually gets indented) and format those.

Format|Cells|alignment tab|Indent as many as I want.

It doesn't adjust automatically (like conditional formatting), but it's pretty
quick if I need to reapply.
 
Thanks Dave,

I do something similar by sorting the data based on the
value contained in the value range and then indenting the
text, after which I reverse the sort. But what I was
really after was a way to do it "on the fly" as it were,
so that if one of the values changes the indenting
automatically changes.

Tony Woods
 
You could use an event macro so that it changes right before your eyes. The one
I chose depends on the user typing in the value--it doesn't if a formula gets
reevaluated.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

With Target
Select Case LCase(.Value)
Case Is = "abcd": .Offset(0, 1).IndentLevel = 1
Case Is = "qwer": .Offset(0, 2).IndentLevel = 2
End Select
End With

End Sub

I indented column B (.offset(0,1)) one level when the value entered in column A
was "abcd".

And if the value were "qwer", then I indented column C (.offset(0,2)) two
levels.

Another alternative would be to wait for all the data entry to be finished, then
come back and indent from there.

The code would look very similar if you looped through the rows.

Option Explicit
Sub testme02()

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
myRng.Offset(, 1).IndentLevel = 0
myRng.Offset(, 2).IndentLevel = 0
For Each myCell In myRng.Cells
With myCell
Select Case LCase(.Value)
Case Is = "abcd": .Offset(0, 1).IndentLevel = 1
Case Is = "qwer": .Offset(0, 2).IndentLevel = 2
End Select
End With
Next myCell
End With

End Sub
 
Back
Top