| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Indent based on character length | Excel Discussion (Misc queries) | |||
| Indent based on Entry | Excel Discussion (Misc queries) | |||
| Format CellsAlignmentIndent | Excel Discussion (Misc queries) | |||
| Macro to Outline based on Indent Level | Excel Programming | |||
| Excel VBA - Best way to indent one column based on another | Excel Programming | |||
Linear Mode
