Formatting a Range of Cells In Excel VBA

Formatting Cells Number

General

Range("A1").NumberFormat = "General"

Number

Range("A1").NumberFormat = "0.00"

Currency

Range("A1").NumberFormat = "$#,##0.00"

Accounting

Range("A1").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

Date

Range("A1").NumberFormat = "yyyy-mm-dd;@"

Time

Range("A1").NumberFormat = "h:mm:ss AM/PM;@"

Percentage

Range("A1").NumberFormat = "0.00%"

Fraction

Range("A1").NumberFormat = "# ?/?"

Scientific

Range("A1").NumberFormat = "0.00E+00"

Text

Range("A1").NumberFormat = "@"

Special

Range("A1").NumberFormat = "00000"

Custom

Range("A1").NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Formatting Cells Alignment

Text Alignment

Horizontal

The value of this property can be set to one of the constants: xlGeneral, xlCenter, xlDistributed, xlJustify, xlLeft, xlRight.

The following code sets the horizontal alignment of cell A1 to center.

Range("A1").HorizontalAlignment = xlCenter

Vertical

The value of this property can be set to one of the constants: xlBottom, xlCenter, xlDistributed, xlJustify, xlTop.

The following code sets the vertical alignment of cell A1 to bottom.

Range("A1").VerticalAlignment = xlBottom

Text Control

Wrap Text

This example formats cell A1 so that the text wraps within the cell.

Range("A1").WrapText = True

Shrink To Fit

This example causes text in row one to automatically shrink to fit in the available column width.

Rows(1).ShrinkToFit = True

Merge Cells

This example merge range A1:A4 to a large one.

Range("A1:A4").MergeCells = True

Right-to-left

Text direction

The value of this property can be set to one of the constants: xlRTL (right-to-left), xlLTR (left-to-right), or xlContext (context).

The following code example sets the reading order of cell A1 to xlRTL (right-to-left).

Range("A1").ReadingOrder = xlRTL

Orientation

The value of this property can be set to an integer value from –90 to 90 degrees or to one of the following constants: xlDownward, xlHorizontal, xlUpward, xlVertical.

The following code example sets the orientation of cell A1 to xlHorizontal.

Range("A1").Orientation = xlHorizontal

Font

Font Name

The value of this property can be set to one of the fonts: Calibri, Times new Roman, Arial...

The following code sets the font name of range A1:A5 to Calibri.

Range("A1:A5").Font.Name = "Calibri"

Font Style

The value of this property can be set to one of the constants: Regular, Bold, Italic, Bold Italic.

The following code sets the font style of range A1:A5 to Italic.

Range("A1:A5").Font.FontStyle = "Italic"

Font Size

The value of this property can be set to an integer value from 1 to 409.

The following code sets the font size of cell A1 to 14.

Range("A1").Font.Size = 14

Underline

The value of this property can be set to one of the constants: xlUnderlineStyleNone, xlUnderlineStyleSingle, xlUnderlineStyleDouble, xlUnderlineStyleSingleAccounting, xlUnderlineStyleDoubleAccounting.

The following code sets the font of cell A1 to xlUnderlineStyleDouble (double underline).

Range("A1").Font.Underline = xlUnderlineStyleDouble

Font Color

The value of this property can be set to one of the standard colors: vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, vbWhite or an integer value from 0 to 16,581,375.

To assist you with specifying the color of anything, the VBA is equipped with a function named RGB. Its syntax is:

Function RGB(RedValue As Byte, GreenValue As Byte, BlueValue As Byte) As long

This function takes three arguments and each must hold a value between 0 and 255. The first argument represents the ratio of red of the color. The second argument represents the green ratio of the color. The last argument represents the blue of the color. After the function has been called, it produces a number whose maximum value can be 255 * 255 * 255 = 16,581,375, which represents a color.

The following code sets the font color of cell A1 to vbBlack (Black).

Range("A1").Font.Color = vbBlack

The following code sets the font color of cell A1 to 0 (Black).

Range("A1").Font.Color = 0

The following code sets the font color of cell A1 to RGB(0, 0, 0) (Black).

Range("A1").Font.Color = RGB(0, 0, 0)

Font Effects

Strikethrough

True if the font is struck through with a horizontal line.

The following code sets the font of cell A1 to strikethrough.

Range("A1").Font.Strikethrough = True

Subscript

True if the font is formatted as subscript. False by default.

The following code sets the font of cell A1 to Subscript.

Range("A1").Font.Subscript = True

Superscript

True if the font is formatted as superscript; False by default.

The following code sets the font of cell A1 to Superscript.

Range("A1").Font.Superscript = True

Border

Border Index

Using VBA you can choose to create borders for the different edges of a range of cells:

  1. xlDiagonalDown (Border running from the upper left-hand corner to the lower right of each cell in the range).
  2. xlDiagonalUp (Border running from the lower left-hand corner to the upper right of each cell in the range).
  3. xlEdgeBottom (Border at the bottom of the range).
  4. xlEdgeLeft (Border at the left-hand edge of the range).
  5. xlEdgeRight (Border at the right-hand edge of the range).
  6. xlEdgeTop (Border at the top of the range).
  7. xlInsideHorizontal (Horizontal borders for all cells in the range except borders on the outside of the range).
  8. xlInsideVertical (Vertical borders for all the cells in the range except borders on the outside of the range).

Line Style

The value of this property can be set to one of the constants: xlContinuous (Continuous line), xlDash (Dashed line), xlDashDot (Alternating dashes and dots), xlDashDotDot (Dash followed by two dots), xlDot (Dotted line), xlDouble (Double line), xlLineStyleNone (No line), xlSlantDashDot (Slanted dashes).

The following code example sets the border on the bottom edge of cell A1 with continuous line.

Range("A1").Borders(xlEdgeBottom).LineStyle = xlContinuous

The following code example removes the border on the bottom edge of cell A1.

Range("A1").Borders(xlEdgeBottom).LineStyle = xlNone

Line Thickness

The value of this property can be set to one of the constants: xlHairline (Hairline, thinnest border), xlMedium (Medium), xlThick (Thick, widest border), xlThin (Thin).

The following code example sets the thickness of the border created to xlThin (Thin).

Range("A1").Borders(xlEdgeBottom).Weight = xlThin

Line Color

The value of this property can be set to one of the standard colors: vbBlack, vbRed, vbGreen, vbYellow, vbBlue, vbMagenta, vbCyan, vbWhite or an integer value from 0 to 16,581,375.

The following code example sets the color of the border on the bottom edge to green.

Range("A1").Borders(xlEdgeBottom).Color = vbGreen

You can also use the RGB function to create a color value.

The following example sets the color of the bottom border of cell A1 with RGB fuction.

Range("A1").Borders(xlEdgeBottom).Color = RGB(255, 0, 0)

Fill

Pattern Style

The value of this property can be set to one of the constants:

  1. xlPatternAutomatic (Excel controls the pattern.)
  2. xlPatternChecker (Checkerboard.)
  3. xlPatternCrissCross (Criss-cross lines.)
  4. xlPatternDown (Dark diagonal lines running from the upper left to the lower right.)
  5. xlPatternGray16 (16% gray.)
  6. xlPatternGray25 (25% gray.)
  7. xlPatternGray50 (50% gray.)
  8. xlPatternGray75 (75% gray.)
  9. xlPatternGray8 (8% gray.)
  10. xlPatternGrid (Grid.)
  11. xlPatternHorizontal (Dark horizontal lines.)
  12. xlPatternLightDown (Light diagonal lines running from the upper left to the lower right.)
  13. xlPatternLightHorizontal (Light horizontal lines.)
  14. xlPatternLightUp (Light diagonal lines running from the lower left to the upper right.)
  15. xlPatternLightVertical (Light vertical bars.)
  16. xlPatternNone (No pattern.)
  17. xlPatternSemiGray75 (75% dark moiré.)
  18. xlPatternSolid (Solid color.)
  19. xlPatternUp (Dark diagonal lines running from the lower left to the upper right.)

Protection

Locking Cells

This property returns True if the object is locked, False if the object can be modified when the sheet is protected, or Null if the specified range contains both locked and unlocked cells.

The following code example unlocks cells A1:B22 on Sheet1 so that they can be modified when the sheet is protected.

Worksheets("Sheet1").Range("A1:B22").Locked = False
Worksheets("Sheet1").Protect

Hiding Formulas

This property returns True if the formula will be hidden when the worksheet is protected, Null if the specified range contains some cells with FormulaHidden equal to True and some cells with FormulaHidden equal to False.

Don’t confuse this property with the Hidden property. The formula will not be hidden if the workbook is protected and the worksheet is not, but only if the worksheet is protected.

The following code example hides the formulas in cells A1 and C1 on Sheet1 when the worksheet is protected.

Worksheets("Sheet1").Range("A1:C1").FormulaHidden = True

Comments

  1. dfg says

    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“M5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“N4:N5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“O4:O5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“P4:P5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“Q3:Q5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    Rows(“3:3”).Select
    Range(“R3”).Activate
    Selection.RowHeight = 20

    Range(“B2:H2”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    Range(“A2:A5”).Select
    With Selection.Font
    .Name = “Calibri”
    .Size = 12
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
    End With
    Range(“I5:M5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Range(“I4:M4”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Range(“N4:P5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    Range(“Q3:Q5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Range(“N4:N5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Columns(“N:N”).ColumnWidth = 14.57
    Columns(“O:O”).ColumnWidth = 14.43
    Columns(“O:O”).ColumnWidth = 12.14
    Columns(“O:O”).ColumnWidth = 10.14
    Columns(“O:O”).ColumnWidth = 12.14
    Range(“O4:O5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Range(“P4:P5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Columns(“P:P”).ColumnWidth = 15.29
    Columns(“P:P”).ColumnWidth = 13.57
    Columns(“P:P”).ColumnWidth = 12.57
    Columns(“P:P”).ColumnWidth = 13.71
    Columns(“Q:Q”).ColumnWidth = 15.57
    Columns(“Q:Q”).ColumnWidth = 14.57
    Range(“Q3:Q5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Columns(“Q:Q”).ColumnWidth = 11.43
    Columns(“Q:Q”).ColumnWidth = 9.71
    Columns(“I:I”).ColumnWidth = 15.71
    Columns(“I:M”).Select
    Selection.ColumnWidth = 10
    Columns(“M:M”).Select
    Selection.ColumnWidth = 14
    Range(“I2:Q2”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Columns(“O:O”).Select
    Selection.ColumnWidth = 14
    Selection.ColumnWidth = 12
    Columns(“N:N”).Select
    Selection.ColumnWidth = 12
    Columns(“P:P”).Select
    Selection.ColumnWidth = 12
    Range(“Q3:Q5”).Select
    Selection.Font.Bold = True
    Selection.Font.Bold = False
    Columns(“Q:Q”).ColumnWidth = 12.86
    ActiveWindow.ScrollColumn = 1
    Rows(“5:5”).Select
    Range(“I5”).Activate
    Selection.RowHeight = 33
    Columns(“I:L”).Select
    Selection.ColumnWidth = 10
    Columns(“M:M”).Select
    Selection.ColumnWidth = 14
    Range(“B3:H5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 90
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    Range(“A2:A5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    End With

    ‘————————————————–
    ‘Set Rows
    ‘————————————————–
    Sheets(“ITWR Details”).Select
    Range(“A2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(“MSR”).Select
    Range(“A6”).Select
    ActiveSheet.Paste
    ‘————————————————–
    ‘Develope Total Row
    ‘————————————————–
    Range(“A6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    Range(“A11:Q11”).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent5
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With

    ‘————————————————–
    ‘Copy Values
    ‘————————————————–

    Sheets(“ITWR Details”).Select
    Range(“B2:F2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(“MSR”).Select
    Range(“B6”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets(“TestExecution”).Select
    Range(“B2:C2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“MSR”).Select
    Range(“G6”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets(“DefectDetails”).Select
    Range(“B2:J2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“MSR”).Select
    Range(“I6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    ‘————————————————–
    ‘Calculate Total Result
    ‘————————————————–

    Range(“B6:Q6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(“B6:Q11”).Select
    Range(“B11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“C11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“D11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“E11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“F11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“G11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“H11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“I11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“J11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“K11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“L11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“M11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“N11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“O11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“P11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“Q11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select

    Range(“B6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    Range(“B11:Q11”).Select
    With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    End With
    Selection.Font.Bold = True

    ‘———————————————————
    ‘Final Formatting
    ‘———————————————————
    Range(“A6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.599993896298105
    .PatternTintAndShade = 0
    End With
    Selection.Copy
    Range(“C6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“E6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“G6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“I6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“K6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“M6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“O6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“Q6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = “`”
    Range(“B6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(“B6:B10”).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
    End With
    Selection.Copy
    Range(“D6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“F6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“H6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“J6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“L6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“N6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Range(“P6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveWindow.ScrollColumn = 1
    Application.CutCopyMode = False

    Range(“A6:Q6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(“A6:Q11”).Select

    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With

    Sheets(“DefectDetails”).Activate
    Range(“J2”).Select
    Selection.Copy
    Sheets(“MSR”).Activate
    Range(“Q6”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 1

    Sheets(“ITWR Details”).Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets(“TestExecution”).Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets(“DefectDetails”).Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets(“MSR”).Activate
    ActiveWindow.DisplayGridlines = False
    Range(“A1”).Select

    End Sub

    • abrar says

      hi
      can you help me with the coding for range N. (ie) the format need to be done until the row which has value

  2. dfg says

    Sub GenerateMSR()

    Sheets(“DefectDetails”).Activate
    Sheets.Add After:=ActiveSheet

    ActiveSheet.Name = “MSR”
    Range(“A2:Q5”).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Rows(“5:5”).Select
    Selection.RowHeight = 30

    ‘—————————————————————————–
    ‘Merge Cells
    ‘—————————————————————————–

    Range(“A2:A5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“B3:B5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Selection.Copy
    Range(“C3”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“D3”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“E3”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“F3”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“G3”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“H3”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range(“B2:F2”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“G2:H2”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“I2:Q2”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“I3:P3”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“I4:M4”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“N4:N5”).Select
    Range(“N5”).Activate
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“O4:O5”).Select
    Range(“O5”).Activate
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“P4:P5”).Select
    Range(“P5”).Activate
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Range(“Q3:Q5”).Select
    Range(“Q5”).Activate
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    ‘————————————————————————–
    ‘Type Header
    ‘————————————————————————–
    Columns(“A:A”).Select
    Selection.ColumnWidth = 30
    Range(“A2:A5”).Select
    ActiveCell.FormulaR1C1 = “App Stream”
    Range(“B2:F2”).Select
    ActiveCell.FormulaR1C1 = “ITWR/JIRA Stories”
    Range(“G2:H2”).Select
    ActiveCell.FormulaR1C1 = “Test Cases Executed”
    Range(“B3:B5”).Select
    ActiveCell.FormulaR1C1 = “Planned”
    Range(“C3:C5”).Select
    ActiveCell.FormulaR1C1 = “Unplanned”
    Range(“D3:D5”).Select
    ActiveCell.FormulaR1C1 = “Backed Out”
    Range(“E3:E5”).Select
    ActiveCell.FormulaR1C1 = “First Time Pass”
    Range(“F3:F5”).Select
    ActiveCell.FormulaR1C1 = “Work In Progress”
    Range(“G3:G5”).Select
    ActiveCell.FormulaR1C1 = “System”
    Range(“H3:H5”).Select
    ActiveCell.FormulaR1C1 = “Regression”
    Range(“I2:Q2”).Select
    ActiveCell.FormulaR1C1 = “Defect Summary”
    Range(“I3:P3”).Select
    ActiveCell.FormulaR1C1 = “QA”
    Range(“I4:M4”).Select
    ActiveCell.FormulaR1C1 = “System Testing”
    Range(“I5”).Select
    ActiveCell.FormulaR1C1 = “Critical”
    Range(“J5”).Select
    ActiveCell.FormulaR1C1 = “High”
    Range(“K5”).Select
    ActiveCell.FormulaR1C1 = “Medium ”
    Range(“L5”).Select
    ActiveCell.FormulaR1C1 = “Low”
    Range(“M5”).Select
    ActiveCell.FormulaR1C1 = “Total Defects”
    Range(“N4:N5”).Select
    ActiveCell.FormulaR1C1 = “Regression Defects”
    Range(“O4:O5”).Select
    ActiveCell.FormulaR1C1 = “Failed on Retest”
    Range(“P4:P5”).Select
    ActiveCell.FormulaR1C1 = “Average Aging ()”
    Range(“P4:P5”).Select
    ActiveCell.FormulaR1C1 = “Average Aging (Days)”
    Range(“P4:P5”).Select
    ActiveCell.FormulaR1C1 = “Average Ageing (Days)”
    Range(“Q3:Q5”).Select
    ActiveCell.FormulaR1C1 = “UAT Defects”
    Range(“R4”).Select
    Columns(“B:B”).EntireColumn.AutoFit
    Columns(“C:C”).EntireColumn.AutoFit
    Columns(“D:D”).EntireColumn.AutoFit
    Columns(“E:E”).EntireColumn.AutoFit
    Columns(“G:G”).EntireColumn.AutoFit
    Columns(“B:I”).Select
    Selection.ColumnWidth = 10
    Columns(“J:Q”).Select
    Selection.ColumnWidth = 10
    Range(“A2:Q5”).Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.Zoom = 90
    With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Columns(“E:E”).Select
    Selection.ColumnWidth = 15
    Columns(“M:Q”).Select
    Selection.ColumnWidth = 20
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Columns(“M:M”).Select
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    Range(“A2:Q5”).Select
    Range(“Q3”).Activate
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range(“I3:P3”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Range(“I4:M4”).Select
    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range(“A2:Q5”).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent5
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    Range(“E3:E5”).Select
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1

    ‘————————————————————-
    ‘Make Vertical Text
    ‘————————————————————-
    Range(“B3:H5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 90
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    Rows(“3:3”).Select
    Range(“R3”).Activate
    Selection.RowHeight = 24
    Rows(“4:4”).Select
    Range(“I4”).Activate
    Selection.RowHeight = 22
    Rows(“5:5”).Select
    Range(“I5”).Activate
    Selection.RowHeight = 30

    ‘————————————————————-
    ‘Arrange Format
    ‘————————————————————-
    Rows(“3:3”).Select
    Selection.RowHeight = 14
    Columns(“B:H”).Select
    Selection.ColumnWidth = 10
    Range(“B3:H5”).Select
    Selection.Font.Bold = False
    Range(“I3:P5”).Select
    Range(“I5”).Activate
    Selection.Font.Bold = False

    ‘————————————————————-
    ‘Arrange Borders Format
    ‘————————————————————-

    Range(“A2:A5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“B3:B5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“C3:C5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“D3:D5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“E3:E5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“F3:F5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“G3:G5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“H3:H5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“B2:F2”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“G2:H2”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“I2:Q2”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“I3:P3”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“I4:M4”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“I5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“J5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“K5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“L5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“M5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“N4:N5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“O4:O5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“P4:P5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range(“Q3:Q5”).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

    Rows(“3:3”).Select
    Range(“R3”).Activate
    Selection.RowHeight = 20

    Range(“B2:H2”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    Range(“A2:A5”).Select
    With Selection.Font
    .Name = “Calibri”
    .Size = 12
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    .ThemeFont = xlThemeFontMinor
    End With
    Range(“I5:M5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Range(“I4:M4”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Range(“N4:P5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    Range(“Q3:Q5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Range(“N4:N5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Columns(“N:N”).ColumnWidth = 14.57
    Columns(“O:O”).ColumnWidth = 14.43
    Columns(“O:O”).ColumnWidth = 12.14
    Columns(“O:O”).ColumnWidth = 10.14
    Columns(“O:O”).ColumnWidth = 12.14
    Range(“O4:O5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Range(“P4:P5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Columns(“P:P”).ColumnWidth = 15.29
    Columns(“P:P”).ColumnWidth = 13.57
    Columns(“P:P”).ColumnWidth = 12.57
    Columns(“P:P”).ColumnWidth = 13.71
    Columns(“Q:Q”).ColumnWidth = 15.57
    Columns(“Q:Q”).ColumnWidth = 14.57
    Range(“Q3:Q5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Columns(“Q:Q”).ColumnWidth = 11.43
    Columns(“Q:Q”).ColumnWidth = 9.71
    Columns(“I:I”).ColumnWidth = 15.71
    Columns(“I:M”).Select
    Selection.ColumnWidth = 10
    Columns(“M:M”).Select
    Selection.ColumnWidth = 14
    Range(“I2:Q2”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True
    End With
    Columns(“O:O”).Select
    Selection.ColumnWidth = 14
    Selection.ColumnWidth = 12
    Columns(“N:N”).Select
    Selection.ColumnWidth = 12
    Columns(“P:P”).Select
    Selection.ColumnWidth = 12
    Range(“Q3:Q5”).Select
    Selection.Font.Bold = True
    Selection.Font.Bold = False
    Columns(“Q:Q”).ColumnWidth = 12.86
    ActiveWindow.ScrollColumn = 1
    Rows(“5:5”).Select
    Range(“I5”).Activate
    Selection.RowHeight = 33
    Columns(“I:L”).Select
    Selection.ColumnWidth = 10
    Columns(“M:M”).Select
    Selection.ColumnWidth = 14
    Range(“B3:H5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 90
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    End With
    Range(“A2:A5”).Select
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    End With

    ‘————————————————–
    ‘Set Rows
    ‘————————————————–
    Sheets(“ITWR Details”).Select
    Range(“A2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(“MSR”).Select
    Range(“A6”).Select
    ActiveSheet.Paste
    ‘————————————————–
    ‘Develope Total Row
    ‘————————————————–
    Range(“A6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    Range(“A11:Q11”).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent5
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With

    ‘————————————————–
    ‘Copy Values
    ‘————————————————–

    Sheets(“ITWR Details”).Select
    Range(“B2:F2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(“MSR”).Select
    Range(“B6”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets(“TestExecution”).Select
    Range(“B2:C2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“MSR”).Select
    Range(“G6”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Sheets(“DefectDetails”).Select
    Range(“B2:J2”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets(“MSR”).Select
    Range(“I6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    ‘————————————————–
    ‘Calculate Total Result
    ‘————————————————–

    Range(“B6:Q6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(“B6:Q11”).Select
    Range(“B11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“C11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“D11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“E11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“F11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“G11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“H11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“I11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“J11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“K11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“L11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“M11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“N11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“O11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“P11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select
    Range(“Q11”).Activate
    ActiveCell.FormulaR1C1 = “=SUM(R[-5]C:R[-1]C)”
    Range(“B6:Q11”).Select

    Range(“B6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    Range(“B11:Q11”).Select
    With Selection.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
    End With
    Selection.Font.Bold = True

    ‘———————————————————
    ‘Final Formatting
    ‘———————————————————
    Range(“A6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.599993896298105
    .PatternTintAndShade = 0
    End With
    Selection.Copy
    Range(“C6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“E6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“G6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“I6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“K6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“M6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“O6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“Q6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False

    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = “`”
    Range(“B6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(“B6:B10”).Select
    With Selection.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent1
    .TintAndShade = 0.799981688894314
    .PatternTintAndShade = 0
    End With
    Selection.Copy
    Range(“D6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“F6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“H6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“J6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“L6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range(“N6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Range(“P6”).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    ActiveWindow.ScrollColumn = 1
    Application.CutCopyMode = False

    Range(“A6:Q6”).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlUp)).Select
    Range(“A6:Q11”).Select

    With Selection
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlMedium
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
    .LineStyle = xlContinuous
    .ThemeColor = 1
    .TintAndShade = 0
    .Weight = xlThin
    End With

    Sheets(“DefectDetails”).Activate
    Range(“J2”).Select
    Selection.Copy
    Sheets(“MSR”).Activate
    Range(“Q6”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 1

    Sheets(“ITWR Details”).Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets(“TestExecution”).Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets(“DefectDetails”).Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets(“MSR”).Activate
    ActiveWindow.DisplayGridlines = False
    Range(“A1”).Select

    End Sub

  3. M says

    How to write Excel VBA macro to format differently cell text in 2 lines and do this for relative text length at each line in the cell
    (there can be from 5 to 100 characters per cell line).

    Example:

    BOLD
    normal
    italic

  4. says

    Всем привет! Красивый у вас сайт!

    Позавчера в yandex нашел s104003 9 в компании prom electric. Рекомендую!

    Всем спасибо!

Trackbacks

Leave a Reply

Your email address will not be published. Required fields are marked *