Indent based on criteria question

spacecaptainsuperguy

Board Regular
Joined
Dec 30, 2004
Messages
202
Office Version
  1. 365
Platform
  1. Windows
I am looking to format a report with successive indents and hope someone can put me on the right path. A previous report I've been working on uses a loop to find headers and make them bold. I figure something like that may work for this, but am unsure.

Here's what I'm wanting to do:
1st look for the word ASSET and begin highlighting every row after it and stop one row prior to finding the word TOTAL ASSETS and indent.

Next look for the word CURRENT ASSETS and begin highlighting every row after it and stop one row prior to finding the word TOTAL CURRENT ASSETS and indent.

I'll go through this a couple more times looking for all the various headers and sub headers under the asset category and then do the whole process over again for the liabilites and equity section.


My prior solution was to just record a macro and do the steps which worked fine until a new line item showed up or an old one dissapeared and screwed up the macro. Now I'm spending more time correcting the macro every month than if I just did it by hand. Hopefully this will be a permanent solution.

thanks in advance for you help
SpaceCap
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Even though I am not into VBA, I believe that you will receive the help you need if you post your present code, so the knowledgeable folk here can do simple insertions of changes to your code. It is very difficult to, out of the blue, post a solution that works, if the question has only vague descriptions of the spreadsheet.
 
Upvote 0
I didn't realize anyone would want to see it. This is the macro I recorded and have had to continually tweak based on the changes to the number of rows in the report over time. Hope you can find this useful.
SpaceCap


Sub HOA_BS()
'
' HOA_BS Macro
' Macro recorded 10/14/2005 by SpaceCaptainSuperGuy
'

'
ActiveCell.FormulaR1C1 = "CT Ranch Community Association"
Range("A1:C2").Select
Selection.UnMerge
Columns("A:A").ColumnWidth = 53
Columns("B:C").Select
Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
Range("A6:A18").Select
Selection.InsertIndent 1
Range("A7:A16").Select
Selection.InsertIndent 1
Range("A8:A12").Select
Selection.InsertIndent 1
Range("A15").Select
Selection.InsertIndent 1
Range("A20:A26").Select
Selection.InsertIndent 1
Range("A21:A22").Select
' Range("A23").Activate
Selection.InsertIndent 1
Range("A25:A25").Select
Selection.InsertIndent 1
Range("6:6,7:7,14:14").Select
Range("A14").Activate
Selection.Insert Shift:=xlDown
Rows("20:20").Select
Selection.Insert Shift:=xlDown
Rows("22:22").Select
Selection.Insert Shift:=xlDown
Rows("24:25").Select
Selection.Insert Shift:=xlDown
Rows("27:27").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=6
Rows("32:32").Select
Selection.Insert Shift:=xlDown
Rows("36:36").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-13
' Range("6:6,8:8,16:16,20:20,22:22,27:27,32:32").Select
' Range("A32").Activate
ActiveWindow.SmallScroll Down:=18
Range("6:6,8:8,16:16,20:20,22:22,27:27,32:32,36:36").Select
Range("A36").Activate
Selection.RowHeight = 8
ActiveWindow.SmallScroll Down:=-98
Range("A5,A23,A26,A37").Select
Range("A37").Activate
Selection.Font.bold = True
Range("B14:C14").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B18:C18").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B20:C20").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B30:C30").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B34:C34").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Range("B23:C23,B37:C37").Select
Range("B37").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.ColumnWidth = 2
Range("A1:D1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A2:D2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveWindow.DisplayGridlines = True
Range("A1:D2").Select
Selection.UnMerge
Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Copy
Columns("E:E").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("D:D").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.ColumnWidth = 13.57
Rows("4:4").EntireRow.AutoFit
Columns("F:F").EntireColumn.AutoFit
Range("A1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Rows("3:3").Select
Selection.Insert Shift:=xlDown
Range("A1:F1").Select

With ActiveSheet.PageSetup
' .LeftHeader = ""
' .CenterHeader = ""
' .RightHeader = ""
.LeftFooter = "Printed on &D"
' .CenterFooter = ""
.RightFooter = "Page &P"
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
' .PrintHeadings = False
' .PrintGridlines = False
' .PrintComments = xlPrintNoComments
' .PrintQuality = -1
.CenterHorizontally = True
' .CenterVertically = False
.Orientation = xlLandscape
' .Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
' .BlackAndWhite = False
' .Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With

ActiveSheet.Name = "Balance Sheet"
Sheets("Balance Sheet").Select

Range("B4:F5").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
 
Upvote 0
By the way...sorry...I don't know how to post such that the formatting of the macro is easier to look at.
SpaceCap
 
Upvote 0
By the way...sorry...I don't know how to post such that the formatting of the macro is easier to look at.
SpaceCap
When you post, precede the macro with the word code in brackets [ ] then end it with /code in brackets (I can't type it out here because it would actually do it instead of explain what it looks like). You can also use the Code button above the box where you compose your post, but you have to watch out because it won't place the BBCode (that is the words inside the brackets) at the cursor, but always at the end of the post, but you can hand-type it anywhere the cursor is.

Without using Code it will left justify every line. Examples:
A
B
C
D
E
F
G
Code:
A
  B
    C
      D
    E
  F
G
Both above were typed with leading spaces, but only the second one displays the leading spaces.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top