Sub DiffFormat()'
' DiffFormat Macro
'
On Error Resume Next
Application.ScreenUpdating = False ' I added This SS
Windows("Report1.xls").Activate
ActiveSheet.Select
Cells.Select
With Selection
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.ColumnWidth = 65.71
Cells.EntireColumn.AutoFit
Columns("C:D").Select
Selection.Delete Shift:=xlToLeft
Range("C1:C2").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Columns("C:D").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("E:F").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("G:G").Select
Selection.Copy
Columns("H:H").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("H4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Diff"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("C1:C2").Select
Selection.Copy
Range("D1").Select
ActiveSheet.Paste
Range("C1:C2").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("D1:H1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("D2:H2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A1:B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A2:B2").Select
Selection.NumberFormat = "[$-409]d-mmm-yy;@"
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A1:B1").Select
ActiveCell.FormulaR1C1 = "=R[4]C[1]"
Range("A1:B1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("5:5").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A4").Select
ActiveCell.FormulaR1C1 = "PO" & Chr(10) & "Number"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("B4").Select
ActiveCell.FormulaR1C1 = "Item" & Chr(10) & "Number"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("C4").Select
ActiveCell.FormulaR1C1 = "Order" & Chr(10) & "Qty"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("D4").Select
ActiveCell.FormulaR1C1 = "Qty" & Chr(10) & "Rcvd"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("E4").Select
ActiveCell.FormulaR1C1 = "Open" & Chr(10) & "Qty"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("F4").Select
ActiveCell.FormulaR1C1 = "Prom" & Chr(10) & "Dlvry"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Range("G4").Select
ActiveCell.FormulaR1C1 = "Need" & Chr(10) & "Date"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Tahoma"
.FontStyle = "Bold"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
Columns("A:G").Select
Range("A3").Activate
Columns("A:G").EntireColumn.AutoFit
Columns("A:H").Select
Columns("A:B").Select
Selection.ColumnWidth = 25#
Range("A3").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("H5").Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$4"
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = ""
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "&9Page &P Of &N"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.869790157480315)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 30
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.View = xlPageBreakPreview
ActiveWindow.Zoom = 100
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
Range("C5").Select
ActiveWindow.FreezePanes = True
Rows("5:40000").Select
Selection.RowHeight = 18
Columns("A:H").Select
Range("A3").Activate
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Call dater
Range("H5").Select
ActiveWindow.SmallScroll Down:=-9
ActiveSheet.Move Before:=Sheets(1)
'FORMATS LINES ON DIFFERENCE SHEET
Static outer, outer1, outer2, line, line1, line2
Application.ScreenUpdating = False
Application.GoTo Reference:="R65000C8"
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
outer1 = ActiveCell.Row
outer = "a4:h" & outer1
outer2 = "a4:a" & outer1
'********** Thin Verticle Lines for selection *********************
line = "a5:a" & outer1 - 1
Range(line).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
line = "b5:b" & outer1 - 1
Range(line).Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
line = "c5:c" & outer1 - 1
Range(line).Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
line = "d5:d" & outer1 - 1
Range(line).Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
line = "e5:e" & outer1 - 1
Range(line).Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
line = "f5:f" & outer1 - 1
Range(line).Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
line = "g5:g" & outer1 - 1
Range(line).Select
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
line = "h5:h" & outer1 - 1
Range(line).Select
With Selection.Borders(xlEdgeRight)
LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThick
End With
'********** Adjust Row Heights *********************
Static rower, rower1, rower2, offsetter, liner3, liner4
offsetter = outer1
line2 = "a5:A" & offsetter
For Each rower In ActiveSheet.Range(line2)
If rower.Value = "" Then
rower.RowHeight = 6
End If
Next rower
'********** Replace Solid Black Horizontal Lines *********************
For Each rower1 In ActiveSheet.Range(outer2)
liner3 = rower1.Address & ":" & rower1.Offset(0, 7).Address
If rower1.Value = "" Then
ActiveSheet.Range(liner3).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlMedium
End With
End If
Next rower1
'********** Replace Solid Black Horizontal Lines *********************
For Each rower2 In ActiveSheet.Range(outer2)
liner4 = rower2.Address & ":" & rower2.Offset(0, 7).Address
If rower2.Value > "" And rower2.Offset(1, 0).Value > "" Then
ActiveSheet.Range(liner4).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDash
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThin
End With
End If
Next rower2
'********** Set Auto Filters *********************
'Range("A4:H4").Select
'Selection.AutoFilter
'********** Outside Thick Border Lines for selection *********************
Range(outer).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThick
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
'.TintAndShade = 0
.Weight = xlThick
End With
Range("D1:H2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Moves Report Header'
Range("D1:D2").Select
Selection.Copy
Range("C1").Select
ActiveSheet.Paste
Range("D1:D2").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C1:F1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("C2:F2").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveSheet.Range("a1").Select