Sub FormatAccessOutput()
Application.ScreenUpdating = False
Cells.Select
Range("D5").Activate
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Insert formulas
Range("L2", Range("L2").End(xlDown)).FormulaR1C1 = "=RC[-5]/RC[-1]"
Range("O2", Range("O2").End(xlDown)).FormulaR1C1 = "=RC[-3]/RC[-1]"
Range("Q2", Range("Q2").End(xlDown)).FormulaR1C1 = "=RC[-10]*RC[-1]"
Range("S2", Range("S2").End(xlDown)).FormulaR1C1 = "=(RC[10])/(RC[6]+RC[7])"
Range("X2", Range("X2").End(xlDown)).FormulaR1C1 = "=(RC[-4]+RC[-3]+RC[5]+RC[-17])/(RC[1]+RC[2])"
Range("AH2", Range("AH2").End(xlDown)).FormulaR1C1 = "=RC[-3]-RC[-27]"
Range("AJ2", Range("AJ2").End(xlDown)).FormulaR1C1 = "=(RC[-5])/RC[-1]"
Range("AK2", Range("AK2").End(xlDown)).FormulaR1C1 = "=RC[-3]/RC[-2]"
Range("AM2", Range("AM2").End(xlDown)).FormulaR1C1 = "=IF(RC[-1]="""", ""N"", (RC[-1]*(RC[-14]+RC[-13])-RC[-19]-RC[-18]-RC[-10]))"
Range("AO2", Range("AO2").End(xlDown)).FormulaR1C1 = "=IF(RC[-1]="""", ""N"", RC[-10]-RC[-1]*RC[-6])"
'Change the format of columns AI and Z to number with no decimal places
Range("AI:AI,Z:Z").NumberFormat = "0"
'Change the format of columns L,O,Q,S,X,AJ,AK to number with two decimal places
Range("L:L,O:O,Q:Q,S:S,X:X,AJ:AJ,AK:AK").NumberFormat = "0.00"
Columns("G:G").Insert Shift:=xlToRight
Range("H1").Select
Selection.Copy
Range("G1").Select
ActiveSheet.Paste
Range("G2", Range("G2").End(xlDown)).FormulaR1C1 = "=(RC[1])"
Columns("N:N").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("N1").FormulaR1C1 = "=(RC[-1])"
Range("N2", Range("N2").End(xlDown)).FormulaR1C1 = "=(RC[-7]/RC[-2])"
Columns("R:R").Insert Shift:=xlToRight
Range("R1").FormulaR1C1 = "=(RC[-1])"
Range("R2", Range("R2").End(xlDown)).FormulaR1C1 = "=(RC[-4]/RC[-2])"
Columns("U:U").Insert Shift:=xlToRight
Range("U1").FormulaR1C1 = "=(RC[-1])"
Range("U2", Range("U2").End(xlDown)).FormulaR1C1 = "=(RC[-14]*RC[-2])"
Columns("AC:AC").Insert Shift:=xlToRight
Range("AC1").FormulaR1C1 = "=(RC[-1])"
Range("AC2", Range("AC2").End(xlDown)).FormulaR1C1 = "=(RC[-5]+RC[-4]+RC[5]+RC[-22])/(RC[1]+RC[2])"
Columns("AN:AN").Insert Shift:=xlToRight
Range("AN1").FormulaR1C1 = "=(RC[-1])"
Range("AN2", Range("AN2").End(xlDown)).FormulaR1C1 = "=(RC[-4]-RC[-33])"
Columns("AR:AR").Insert Shift:=xlToRight
Range("AR1").FormulaR1C1 = "=(RC[-1])"
Range("AR2", Range("AR2").End(xlDown)).FormulaR1C1 = "=(RC[-4]/RC[-3])"
Columns("BB:BD").Select
Selection.Cut
Columns("L:L").Insert Shift:=xlToRight
Columns("M:N").Select
Selection.Cut
Columns("R:R").Insert Shift:=xlToRight
Columns("J:J").Select
Selection.Cut
Columns("D:D").Insert Shift:=xlToRight
Range("N2").Select
Selection.AutoFilter
ActiveWindow.FreezePanes = True
'Highlight columns H,O,U,X yellow
Range("H1", Range("H1").End(xlDown)).Interior.ColorIndex = 36
Range("O1", Range("O1").End(xlDown)).Interior.ColorIndex = 36
Range("U1", Range("U1").End(xlDown)).Interior.ColorIndex = 36
Range("X1", Range("X1").End(xlDown)).Interior.ColorIndex = 36
'Highlight columns AM,AQ,AU light green
Range("AM1", Range("AM1").End(xlDown)).Interior.ColorIndex = 35
Range("AQ1", Range("AQ1").End(xlDown)).Interior.ColorIndex = 35
Range("AU1", Range("AU1").End(xlDown)).Interior.ColorIndex = 35
'Highlight column AE light blue
Range("AE1", Range("AE1").End(xlDown)).Interior.ColorIndex = 33
'Highlight column AF blue
Range("AF1", Range("AF1").End(xlDown)).Interior.ColorIndex = 34
'Highlight column AD grey
Range("AD1", Range("AD1").End(xlDown)).Interior.ColorIndex = 15
Application.ScreenUpdating = True
End Sub
Bookmarks