New to VBA

[barmacost]

I'm really new to VBA and I think I have a pretty basic need but can't quite figure it out.


If a row in column 1 has a particular letter (each entry will only be 1 letter) then I want the same row for column 2 to have a certain level on indention based on the letter. I found some code on the internet that will search for specific text in column 1 and then indent column 1 based on the text but how do I alter it so that the indention occurs in column 2 instead?


Private Sub Worksheet_Change(ByVal Target As Excel.Range)


If Target.Column = 1 Then

If Target = "O" Then


Target.IndentLevel = 1

Else


If Target = "G" Then

Target.IndentLevel = 3


Else


If Target = "T" Then

Target.IndentLevel = 5


End If

End If

End If

End If


End Sub

 

[Luke M]

Change "Target.IndentLevel" to "Target.Offset(0,1).IndentLevel"

The Offset tells VB to go 0 cells down (negative for up) and 1 row to the right (negative to go left)

 

[barmacost]

Perfect! Thanks for the education.

 

[Luke M]

It wasn't your question, but in the interest of sharing knowledge...another way to write your code so you don't end up with so many If's is to use either ElseIf or Case Select (my preference), like so:

[pre]

Private Sub Worksheet_Change(ByVal Target)
If Target.Column <> 1 Then Exit Sub
Select Case Target
Case "O"
x = 1
Case "G"
x = 3
Case "T"
x = 5
Case Else
x = 0 'No letter found
End Select
Target.Offset(0, 1).IndexLevel = x
End Sub

[/pre]