Indent and format based on Col A
 

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?

Indent and format based on Col A
 

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" wrote in message
...
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?

Indent and format based on Col A
 

Peter,
Thanks so much. This works perfectly.
Cheryl

"Peter T" wrote:

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" wrote in message
...
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?

Indent and format based on Col A
 

Glad it works.

Not important but you can change

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

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

Regards,
Peter T

"CB" wrote in message
...
Peter,
Thanks so much. This works perfectly.
Cheryl

"Peter T" wrote:

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" wrote in message
...
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?

Indent and format based on Col A
 

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

"Peter T" wrote:

Glad it works.

Not important but you can change

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

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

Regards,
Peter T

"CB" wrote in message
...
Peter,
Thanks so much. This works perfectly.
Cheryl

"Peter T" wrote:

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" wrote in message
...
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?