SonicBoomGolf
Active Member
- Joined
- Aug 7, 2004
- Messages
- 325
This code is working for me, but I just wanted to see if the pros could give me any suggestions on if this could be cleaned up and simplified a bit (the border portion of my code in particular). I imagine that there are some areas where processes can be done more efficiently, but not sure where. I took this code straight from the file, and have left my comments included also. Let me know if you have any ideas. Thanks.
Sub Macro5()
Finalrow = Range("A65536").End(xlUp).Row
' Text to Columns
Columns("a:a").Select
Range("a" & Finalrow).Activate
Selection.TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
' Freeze Panes
Range("e2").Select
ActiveWindow.FreezePanes = True
' Inserts New Column in Column A
With Range("a:a").Select
Selection.Insert Shift:=xlToRight
End With
' Inserts New Row In Row 1
With Rows("1:1").Select
Selection.Insert Shift:=xlDown
End With
' Enter "Rank" in A2
Range("A2").Value = "Rank"
' Gives all cells 8 Point Font
Cells.Select
Range("A1").Activate
With Selection.Font
.Name = "Arial"
.Size = 8
End With
' Merge Cells (Multiple Ranges)
[f1:m1, n1:u1, v1:ac1, ad1:ak1, al1:as1, at1:ba1, bb1:bi1, cm1:co1].Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
Selection.Font.Bold = True
' Headers in Row 1
Range("f1").Value = "Ft. Worth"
Range("n1").Value = "Indianapolis"
Range("v1").Value = "Jacksonville"
Range("ad1").Value = "Knoxville"
Range("al1").Value = "Modesto"
Range("at1").Value = "Ontario"
Range("bb1").Value = "Network Summary"
' Headers in Row 2
Range("F2, N2, V2, ad2, al2, at2, bb2").Value = "Cases Shipped"
Range("g2, o2, w2, ae2, am2, au2, bc2").Value = "Cases Received"
Range("h2, p2, x2, af2, an2, av2, bd2").Value = "Total Cases Handled (TCH)"
Range("i2, q2, y2, ag2, ao2, aw2, be2").Value = "WHSE Damage"
Range("j2, r2, z2, ah2, ap2, ax2, bf2").Value = "SHIP Damage"
Range("k2, s2, aa2, ai2, aq2, ay2, bg2").Value = "CARR Damage"
Range("l2, t2, ab2, aj2, ar2, az2, bh2").Value = "Total Damage"
Range("m2, u2, ac2, ak2, as2, ba2, bi2").Value = "Damage as a % of TCH"
Range("bj2").Value = "National Standard Price"
Range("bk2").Value = "Additional Trans/Labor Cost Per Case"
Range("bl2").Value = "Total Cost Per Damaged Case"
Range("bm2").Value = "Total Damage Cost"
Range("bn2").Value = "Ft. Worth"
Range("br2").Value = "Indianapolis"
Range("bv2").Value = "Jacksonville"
Range("bz2").Value = "Knoxville"
Range("cd2").Value = "Modesto"
Range("ch2").Value = "Ontario"
Range("cl2").Value = "Network Summary"
Range("bo2, bs2, bw2, ca2, ce2, ci2, cm2").Value = "WHSE Damage $'s"
Range("bp2, bt2, bx2, cb2, cf2, cj2, cn2").Value = "SHIP Damage $'s"
Range("bq2, bw, by2, cc2, cg2, ck2, co2").Value = "CARR Damage $'s"
Range("cm1").Value = "Network Damage Summary"
With Rows("2:2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
' Puts borders around all cells
Range(Cells(1, 1), Cells(Finalrow + 1, 61)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' Formatting of cells F - BI
Range(Cells(1, 6), Cells(Finalrow + 1, 13)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 14), Cells(Finalrow + 1, 21)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 22), Cells(Finalrow + 1, 29)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 30), Cells(Finalrow + 1, 37)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 38), Cells(Finalrow + 1, 45)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 46), Cells(Finalrow + 1, 53)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 54), Cells(Finalrow + 1, 61)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlThick
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = 2
End With
' All the formulas in columns F through I
Range("bj3:bj" & Finalrow).Formula = "=IF(ISNA(VLOOKUP(B3,SAP_NSC_Upload!$A$1:$B$6209,2,FALSE)),0,VLOOKUP(B3,SAP_NSC_Upload!$A$1:$B$6209,2,FALSE))"
Range("bk3:bk" & Finalrow).Formula = ".85"
Range("bl3:bl" & Finalrow).Formula = "=BK3+BJ3"
Range("bm3:bm" & Finalrow).Formula = "=BL3*BH3"
Range(Cells(3, 62), Cells(Finalrow + 1, 93)).NumberFormat = "$#,##0.00"
Range(Cells(1, 65), Cells(Finalrow, 65)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
' Formats Row 2 W/ Bold and Underline
Rows("1:2").Font.Bold = True
With Rows("1:2").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
' Formats the numbers with commas
Range(Cells(3, 6), Cells(Finalrow + 1, 61)).NumberFormat = "#,##0"
' Adjust Column Width in Columns A-E
Columns("E:E").EntireColumn.AutoFit
Columns("D:D").ColumnWidth = 4.29
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 5.29
' Adjust Column Width in Columns F-BI
Columns("F:BI").Select
Range("BI2").Activate
Selection.ColumnWidth = 8#
' Adjust Column Width in Columns BJ-BM
Columns("BJ:CO").Select
Range("BJ2").Activate
Selection.ColumnWidth = 10.29
' Fills Cells W/Black
Range("A1:e1").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Range(Cells(Finalrow + 1, 3), Cells(Finalrow + 1, 5)).Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
' Damage as a % of TCH Formulas
Range("m3:m" & Finalrow + 1).Formula = "=IF(H3>0,L3/H3,""N/A"")"
Range("u3:u" & Finalrow + 1).Formula = "=IF(P3>0,T3/P3,""N/A"")"
Range("ac3:ac" & Finalrow + 1).Formula = "=IF(X3>0,AB3/X3,""N/A"")"
Range("ak3:ak" & Finalrow + 1).Formula = "=IF(AF3>0,AJ3/AF3,""N/A"")"
Range("as3:as" & Finalrow + 1).Formula = "=IF(An3>0,Ar3/An3,""N/A"")"
Range("ba3:ba" & Finalrow + 1).Formula = "=IF(AV3>0,AZ3/AV3,""N/A"")"
Range("bi3:bi" & Finalrow + 1).Formula = "=IF(BD3>0,BH3/BD3,""N/A"")"
' Sums up the dollars of damage
Range("bm" & Finalrow + 1).Formula = "=sum(bm3:bm" & Finalrow & ")"
' Thick Line for Bottom Row
Range(Cells(Finalrow + 1, 1), Cells(Finalrow + 1, 71)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Bold = True
End With
' Formulas to Calculate $'s of Damages by Location
Range("bn3:bn" & Finalrow + 1).Formula = "=BL3*l3"
Range("bo3:bo" & Finalrow + 1).Formula = "=BL3*i3"
Range("bp3:bp" & Finalrow + 1).Formula = "=BL3*j3"
Range("bq3:bq" & Finalrow + 1).Formula = "=BL3*k3"
Range("br3:br" & Finalrow + 1).Formula = "=BL3*t3"
Range("bs3:bs" & Finalrow + 1).Formula = "=BL3*q3"
Range("bt3:bt" & Finalrow + 1).Formula = "=BL3*r3"
Range("bu3:bu" & Finalrow + 1).Formula = "=BL3*s3"
Range("bv3:bv" & Finalrow + 1).Formula = "=BL3*ab3"
Range("bw3:bw" & Finalrow + 1).Formula = "=BL3*y3"
Range("bx3:bx" & Finalrow + 1).Formula = "=BL3*z3"
Range("by3:by" & Finalrow + 1).Formula = "=BL3*aa3"
Range("bz3:bz" & Finalrow + 1).Formula = "=BL3*aj3"
Range("ca3:ca" & Finalrow + 1).Formula = "=BL3*ag3"
Range("cb3:cb" & Finalrow + 1).Formula = "=BL3*ah3"
Range("cc3:cc" & Finalrow + 1).Formula = "=BL3*ai3"
Range("cd3:cd" & Finalrow + 1).Formula = "=BL3*ar3"
Range("ce3:ce" & Finalrow + 1).Formula = "=BL3*ao3"
Range("cf3:cf" & Finalrow + 1).Formula = "=BL3*ap3"
Range("cg3:cg" & Finalrow + 1).Formula = "=BL3*aq3"
Range("ch3:ch" & Finalrow + 1).Formula = "=BL3*az3"
Range("ci3:ci" & Finalrow + 1).Formula = "=BL3*aw3"
Range("cj3:cj" & Finalrow + 1).Formula = "=BL3*ax3"
Range("ck3:ck" & Finalrow + 1).Formula = "=BL3*ay3"
Range("cl3:cl" & Finalrow + 1).Formula = "=BL3*bh3"
Range("cm3:cm" & Finalrow + 1).Formula = "=BL3*be3"
Range("cn3:cn" & Finalrow + 1).Formula = "=BL3*bf3"
Range("co3:co" & Finalrow + 1).Formula = "=BL3*bg3"
' Sums up the Dollars of Damage by Location
Range("bn" & Finalrow + 1).Formula = "=sum(bn3:bn" & Finalrow & ")"
Range("bo" & Finalrow + 1).Formula = "=sum(bo3:bo" & Finalrow & ")"
Range("bp" & Finalrow + 1).Formula = "=sum(bp3:bp" & Finalrow & ")"
Range("bq" & Finalrow + 1).Formula = "=sum(bq3:bq" & Finalrow & ")"
Range("br" & Finalrow + 1).Formula = "=sum(br3:br" & Finalrow & ")"
Range("bs" & Finalrow + 1).Formula = "=sum(bs3:bs" & Finalrow & ")"
Range("bt" & Finalrow + 1).Formula = "=sum(bt3:bt" & Finalrow & ")"
Range("bu" & Finalrow + 1).Formula = "=sum(bu3:bu" & Finalrow & ")"
Range("bv" & Finalrow + 1).Formula = "=sum(bv3:bv" & Finalrow & ")"
Range("bw" & Finalrow + 1).Formula = "=sum(bw3:bw" & Finalrow & ")"
Range("bx" & Finalrow + 1).Formula = "=sum(bx3:bx" & Finalrow & ")"
Range("by" & Finalrow + 1).Formula = "=sum(by3:by" & Finalrow & ")"
Range("bz" & Finalrow + 1).Formula = "=sum(bz3:bz" & Finalrow & ")"
Range("ca" & Finalrow + 1).Formula = "=sum(ca3:ca" & Finalrow & ")"
Range("cb" & Finalrow + 1).Formula = "=sum(cb3:cb" & Finalrow & ")"
Range("cc" & Finalrow + 1).Formula = "=sum(cc3:cc" & Finalrow & ")"
Range("cd" & Finalrow + 1).Formula = "=sum(cd3:cd" & Finalrow & ")"
Range("ce" & Finalrow + 1).Formula = "=sum(ce3:ce" & Finalrow & ")"
Range("cf" & Finalrow + 1).Formula = "=sum(cf3:cf" & Finalrow & ")"
Range("cg" & Finalrow + 1).Formula = "=sum(cg3:cg" & Finalrow & ")"
Range("ch" & Finalrow + 1).Formula = "=sum(ch3:ch" & Finalrow & ")"
Range("ci" & Finalrow + 1).Formula = "=sum(ci3:ci" & Finalrow & ")"
Range("cj" & Finalrow + 1).Formula = "=sum(cj3:cj" & Finalrow & ")"
Range("ck" & Finalrow + 1).Formula = "=sum(ck3:ck" & Finalrow & ")"
Range("cl" & Finalrow + 1).Formula = "=sum(cl3:cl" & Finalrow & ")"
Range("cm" & Finalrow + 1).Formula = "=sum(cm3:cm" & Finalrow & ")"
Range("cn" & Finalrow + 1).Formula = "=sum(cn3:cn" & Finalrow & ")"
Range("co" & Finalrow + 1).Formula = "=sum(co3:co" & Finalrow & ")"
' Color Location Rows Again
Range(Cells(1, 66), Cells(Finalrow + 1, 69)).Interior.ColorIndex = 6
Range(Cells(1, 70), Cells(Finalrow + 1, 73)).Interior.ColorIndex = 40
Range(Cells(1, 74), Cells(Finalrow + 1, 77)).Interior.ColorIndex = 36
Range(Cells(1, 78), Cells(Finalrow + 1, 81)).Interior.ColorIndex = 35
Range(Cells(1, 82), Cells(Finalrow + 1, 85)).Interior.ColorIndex = 8
Range(Cells(1, 86), Cells(Finalrow + 1, 89)).Interior.ColorIndex = 38
' Formatting of Cells as $'s
Range(Cells(2, 66), Cells(Finalrow + 1, 71)).NumberFormat = "$#,##0.00"
End Sub
Sub Macro5()
Finalrow = Range("A65536").End(xlUp).Row
' Text to Columns
Columns("a:a").Select
Range("a" & Finalrow).Activate
Selection.TextToColumns Destination:=Range("a1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
' Freeze Panes
Range("e2").Select
ActiveWindow.FreezePanes = True
' Inserts New Column in Column A
With Range("a:a").Select
Selection.Insert Shift:=xlToRight
End With
' Inserts New Row In Row 1
With Rows("1:1").Select
Selection.Insert Shift:=xlDown
End With
' Enter "Rank" in A2
Range("A2").Value = "Rank"
' Gives all cells 8 Point Font
Cells.Select
Range("A1").Activate
With Selection.Font
.Name = "Arial"
.Size = 8
End With
' Merge Cells (Multiple Ranges)
[f1:m1, n1:u1, v1:ac1, ad1:ak1, al1:as1, at1:ba1, bb1:bi1, cm1:co1].Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = True
End With
Selection.Font.Bold = True
' Headers in Row 1
Range("f1").Value = "Ft. Worth"
Range("n1").Value = "Indianapolis"
Range("v1").Value = "Jacksonville"
Range("ad1").Value = "Knoxville"
Range("al1").Value = "Modesto"
Range("at1").Value = "Ontario"
Range("bb1").Value = "Network Summary"
' Headers in Row 2
Range("F2, N2, V2, ad2, al2, at2, bb2").Value = "Cases Shipped"
Range("g2, o2, w2, ae2, am2, au2, bc2").Value = "Cases Received"
Range("h2, p2, x2, af2, an2, av2, bd2").Value = "Total Cases Handled (TCH)"
Range("i2, q2, y2, ag2, ao2, aw2, be2").Value = "WHSE Damage"
Range("j2, r2, z2, ah2, ap2, ax2, bf2").Value = "SHIP Damage"
Range("k2, s2, aa2, ai2, aq2, ay2, bg2").Value = "CARR Damage"
Range("l2, t2, ab2, aj2, ar2, az2, bh2").Value = "Total Damage"
Range("m2, u2, ac2, ak2, as2, ba2, bi2").Value = "Damage as a % of TCH"
Range("bj2").Value = "National Standard Price"
Range("bk2").Value = "Additional Trans/Labor Cost Per Case"
Range("bl2").Value = "Total Cost Per Damaged Case"
Range("bm2").Value = "Total Damage Cost"
Range("bn2").Value = "Ft. Worth"
Range("br2").Value = "Indianapolis"
Range("bv2").Value = "Jacksonville"
Range("bz2").Value = "Knoxville"
Range("cd2").Value = "Modesto"
Range("ch2").Value = "Ontario"
Range("cl2").Value = "Network Summary"
Range("bo2, bs2, bw2, ca2, ce2, ci2, cm2").Value = "WHSE Damage $'s"
Range("bp2, bt2, bx2, cb2, cf2, cj2, cn2").Value = "SHIP Damage $'s"
Range("bq2, bw, by2, cc2, cg2, ck2, co2").Value = "CARR Damage $'s"
Range("cm1").Value = "Network Damage Summary"
With Rows("2:2")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
' Puts borders around all cells
Range(Cells(1, 1), Cells(Finalrow + 1, 61)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
' Formatting of cells F - BI
Range(Cells(1, 6), Cells(Finalrow + 1, 13)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 14), Cells(Finalrow + 1, 21)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 22), Cells(Finalrow + 1, 29)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 30), Cells(Finalrow + 1, 37)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 38), Cells(Finalrow + 1, 45)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 46), Cells(Finalrow + 1, 53)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Range(Cells(1, 54), Cells(Finalrow + 1, 61)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlDash
.Weight = xlMedium
.ColorIndex = 1
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDash
.Weight = xlThick
.ColorIndex = 1
End With
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = 2
End With
' All the formulas in columns F through I
Range("bj3:bj" & Finalrow).Formula = "=IF(ISNA(VLOOKUP(B3,SAP_NSC_Upload!$A$1:$B$6209,2,FALSE)),0,VLOOKUP(B3,SAP_NSC_Upload!$A$1:$B$6209,2,FALSE))"
Range("bk3:bk" & Finalrow).Formula = ".85"
Range("bl3:bl" & Finalrow).Formula = "=BK3+BJ3"
Range("bm3:bm" & Finalrow).Formula = "=BL3*BH3"
Range(Cells(3, 62), Cells(Finalrow + 1, 93)).NumberFormat = "$#,##0.00"
Range(Cells(1, 65), Cells(Finalrow, 65)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
' Formats Row 2 W/ Bold and Underline
Rows("1:2").Font.Bold = True
With Rows("1:2").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
' Formats the numbers with commas
Range(Cells(3, 6), Cells(Finalrow + 1, 61)).NumberFormat = "#,##0"
' Adjust Column Width in Columns A-E
Columns("E:E").EntireColumn.AutoFit
Columns("D:D").ColumnWidth = 4.29
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 5.29
' Adjust Column Width in Columns F-BI
Columns("F:BI").Select
Range("BI2").Activate
Selection.ColumnWidth = 8#
' Adjust Column Width in Columns BJ-BM
Columns("BJ:CO").Select
Range("BJ2").Activate
Selection.ColumnWidth = 10.29
' Fills Cells W/Black
Range("A1:e1").Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
Range(Cells(Finalrow + 1, 3), Cells(Finalrow + 1, 5)).Select
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
End With
' Damage as a % of TCH Formulas
Range("m3:m" & Finalrow + 1).Formula = "=IF(H3>0,L3/H3,""N/A"")"
Range("u3:u" & Finalrow + 1).Formula = "=IF(P3>0,T3/P3,""N/A"")"
Range("ac3:ac" & Finalrow + 1).Formula = "=IF(X3>0,AB3/X3,""N/A"")"
Range("ak3:ak" & Finalrow + 1).Formula = "=IF(AF3>0,AJ3/AF3,""N/A"")"
Range("as3:as" & Finalrow + 1).Formula = "=IF(An3>0,Ar3/An3,""N/A"")"
Range("ba3:ba" & Finalrow + 1).Formula = "=IF(AV3>0,AZ3/AV3,""N/A"")"
Range("bi3:bi" & Finalrow + 1).Formula = "=IF(BD3>0,BH3/BD3,""N/A"")"
' Sums up the dollars of damage
Range("bm" & Finalrow + 1).Formula = "=sum(bm3:bm" & Finalrow & ")"
' Thick Line for Bottom Row
Range(Cells(Finalrow + 1, 1), Cells(Finalrow + 1, 71)).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Bold = True
End With
' Formulas to Calculate $'s of Damages by Location
Range("bn3:bn" & Finalrow + 1).Formula = "=BL3*l3"
Range("bo3:bo" & Finalrow + 1).Formula = "=BL3*i3"
Range("bp3:bp" & Finalrow + 1).Formula = "=BL3*j3"
Range("bq3:bq" & Finalrow + 1).Formula = "=BL3*k3"
Range("br3:br" & Finalrow + 1).Formula = "=BL3*t3"
Range("bs3:bs" & Finalrow + 1).Formula = "=BL3*q3"
Range("bt3:bt" & Finalrow + 1).Formula = "=BL3*r3"
Range("bu3:bu" & Finalrow + 1).Formula = "=BL3*s3"
Range("bv3:bv" & Finalrow + 1).Formula = "=BL3*ab3"
Range("bw3:bw" & Finalrow + 1).Formula = "=BL3*y3"
Range("bx3:bx" & Finalrow + 1).Formula = "=BL3*z3"
Range("by3:by" & Finalrow + 1).Formula = "=BL3*aa3"
Range("bz3:bz" & Finalrow + 1).Formula = "=BL3*aj3"
Range("ca3:ca" & Finalrow + 1).Formula = "=BL3*ag3"
Range("cb3:cb" & Finalrow + 1).Formula = "=BL3*ah3"
Range("cc3:cc" & Finalrow + 1).Formula = "=BL3*ai3"
Range("cd3:cd" & Finalrow + 1).Formula = "=BL3*ar3"
Range("ce3:ce" & Finalrow + 1).Formula = "=BL3*ao3"
Range("cf3:cf" & Finalrow + 1).Formula = "=BL3*ap3"
Range("cg3:cg" & Finalrow + 1).Formula = "=BL3*aq3"
Range("ch3:ch" & Finalrow + 1).Formula = "=BL3*az3"
Range("ci3:ci" & Finalrow + 1).Formula = "=BL3*aw3"
Range("cj3:cj" & Finalrow + 1).Formula = "=BL3*ax3"
Range("ck3:ck" & Finalrow + 1).Formula = "=BL3*ay3"
Range("cl3:cl" & Finalrow + 1).Formula = "=BL3*bh3"
Range("cm3:cm" & Finalrow + 1).Formula = "=BL3*be3"
Range("cn3:cn" & Finalrow + 1).Formula = "=BL3*bf3"
Range("co3:co" & Finalrow + 1).Formula = "=BL3*bg3"
' Sums up the Dollars of Damage by Location
Range("bn" & Finalrow + 1).Formula = "=sum(bn3:bn" & Finalrow & ")"
Range("bo" & Finalrow + 1).Formula = "=sum(bo3:bo" & Finalrow & ")"
Range("bp" & Finalrow + 1).Formula = "=sum(bp3:bp" & Finalrow & ")"
Range("bq" & Finalrow + 1).Formula = "=sum(bq3:bq" & Finalrow & ")"
Range("br" & Finalrow + 1).Formula = "=sum(br3:br" & Finalrow & ")"
Range("bs" & Finalrow + 1).Formula = "=sum(bs3:bs" & Finalrow & ")"
Range("bt" & Finalrow + 1).Formula = "=sum(bt3:bt" & Finalrow & ")"
Range("bu" & Finalrow + 1).Formula = "=sum(bu3:bu" & Finalrow & ")"
Range("bv" & Finalrow + 1).Formula = "=sum(bv3:bv" & Finalrow & ")"
Range("bw" & Finalrow + 1).Formula = "=sum(bw3:bw" & Finalrow & ")"
Range("bx" & Finalrow + 1).Formula = "=sum(bx3:bx" & Finalrow & ")"
Range("by" & Finalrow + 1).Formula = "=sum(by3:by" & Finalrow & ")"
Range("bz" & Finalrow + 1).Formula = "=sum(bz3:bz" & Finalrow & ")"
Range("ca" & Finalrow + 1).Formula = "=sum(ca3:ca" & Finalrow & ")"
Range("cb" & Finalrow + 1).Formula = "=sum(cb3:cb" & Finalrow & ")"
Range("cc" & Finalrow + 1).Formula = "=sum(cc3:cc" & Finalrow & ")"
Range("cd" & Finalrow + 1).Formula = "=sum(cd3:cd" & Finalrow & ")"
Range("ce" & Finalrow + 1).Formula = "=sum(ce3:ce" & Finalrow & ")"
Range("cf" & Finalrow + 1).Formula = "=sum(cf3:cf" & Finalrow & ")"
Range("cg" & Finalrow + 1).Formula = "=sum(cg3:cg" & Finalrow & ")"
Range("ch" & Finalrow + 1).Formula = "=sum(ch3:ch" & Finalrow & ")"
Range("ci" & Finalrow + 1).Formula = "=sum(ci3:ci" & Finalrow & ")"
Range("cj" & Finalrow + 1).Formula = "=sum(cj3:cj" & Finalrow & ")"
Range("ck" & Finalrow + 1).Formula = "=sum(ck3:ck" & Finalrow & ")"
Range("cl" & Finalrow + 1).Formula = "=sum(cl3:cl" & Finalrow & ")"
Range("cm" & Finalrow + 1).Formula = "=sum(cm3:cm" & Finalrow & ")"
Range("cn" & Finalrow + 1).Formula = "=sum(cn3:cn" & Finalrow & ")"
Range("co" & Finalrow + 1).Formula = "=sum(co3:co" & Finalrow & ")"
' Color Location Rows Again
Range(Cells(1, 66), Cells(Finalrow + 1, 69)).Interior.ColorIndex = 6
Range(Cells(1, 70), Cells(Finalrow + 1, 73)).Interior.ColorIndex = 40
Range(Cells(1, 74), Cells(Finalrow + 1, 77)).Interior.ColorIndex = 36
Range(Cells(1, 78), Cells(Finalrow + 1, 81)).Interior.ColorIndex = 35
Range(Cells(1, 82), Cells(Finalrow + 1, 85)).Interior.ColorIndex = 8
Range(Cells(1, 86), Cells(Finalrow + 1, 89)).Interior.ColorIndex = 38
' Formatting of Cells as $'s
Range(Cells(2, 66), Cells(Finalrow + 1, 71)).NumberFormat = "$#,##0.00"
End Sub