Indent and format based on Col A

[CB]

5
Hello,
I have a spreadsheet that has numbers in column A and Text in column B. I
would like to indent Column B based on the value in Column A as follows:
If Column A Contains Indent Column B # spaces
1 0
2 5
3 10
4 15
5 20
6 25

Also Column K contains the text yes or no. If Column K = yes Format Col B:J
font = bold and shade cell light gray.

Can someone help me?

 

[Peter T]

Sub test()
Dim b As Boolean, v
Dim nd As Long
Dim lastRow As Long
Dim rng As Range, cel As Range

With ActiveSheet
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
Set rng = .Range("A1:A" & lastRow)
End With

For Each cel In rng
With cel
nd = CLng(.Value)
If Val(nd) >= 1 And Val(nd) <= 6 Then
nd = nd - 1
Else
nd = 0
End If
b = UCase(.Offset(, 10)) = "YES"

With .Offset(, 1)
If .IndentLevel <> nd Then .IndentLevel = nd

With .Resize(, 9)
v = .Font.Bold
If IsNull(v) Then
.Font.Bold = b
ElseIf v <> b Then
.Font.Bold = b
End If
End With

End With

End With
Next

End Sub

Regards,
Peter T

 

[CB]

Peter,
Thanks so much. This works perfectly.
Cheryl

 

[Peter T]

Glad it works.

Not important but you can change

to
nd = CLng(.Value)
If nd >= 1 And nd <= 6 Then

Regards,
Peter T

 

[CB]

Thanks againg Peter. I'll give the change a shot also.
Cheers