METBD 050VBA – Try It Out #2Learning Objectives:1. How to align text in a range using VBA code2. How to add borders to a range 3. Learn the use of the Immediate Window of the VBE4. How to set the interior color for a range5. How to change the name, size and font style of text in a range6. How to merge cells7. Using number format8. Inserting and naming a worksheet1 – Alignment in a Cell There are two properties of a range (cell) that control the alignment of the cell’s contents. They are HorizontalAlignment and VerticalAlignment. Each of these properties has three possible values. In thehorizontal direction, the options are xlLeft, xlCenter, and xlRight. Vertically, the options are xlTop, xlBottom and xlCenter. For example, to center the contents of cell D10 horizontally, the code is:[D10].HorizontalAlignment = xlCenterGive it a try:In cells B4:B9 of a worksheet place the numbers 1 – 6. Enter the Visual Basic Editor (VBE) and insert a new module into the project. In the module, start a new procedure calling it AlignThem. The following code aligns the numbers. [B4].HorizontalAlignment = xlLeft ‘left alignment[B5].HorizontalAlignment = xlCenter ‘center alignment[B6].HorizontalAlignment = xlRight ‘right alignment[B7].VerticalAlignment = xlTop‘top alignment[B8]. VerticalAlignment = xlCenter ‘center alignment[B9]. VerticalAlignment = xlBottom ‘bottom alignment[B4:B9].RowHeight = 20 ‘changes row height to 20 pointsExit the VBE. On the Visual Basic toolbar, pick the RUN the program. 2 – Borders A cell has 6 borders which are collectively known as the Borders. Each of the borders have three properties that can be set using VB code. These properties are LineStyle, Weight, and ColorIndex. The names of the borders are: xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, orxlEdgeTop, xlInsideHorizontal, or xlInsideVertical.  The following are the options available for each of the properties: LineStyle Options for Borders: xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlSlantDashDot, or xlLineStyleNone Line Weight for Borders: xlHairline, xlThin, xlMedium, or xlThick ColorIndex for Borders: There are 57 possible colorindex colors available. The colors and their corresponding numbers are shown in the figure below.HINT: Be sure to usecopy and paste wheninserting the statements.ColorIndex ValuesGive it a try:In a module of your workbook, start a new procedure calling it NewBorder. The following code aligns the numbers. [B4].Borders(xlEdgeBottom).LineStyle = xlDouble ‘bottom is double line[B4].Borders(xlEdgeRight).Linestyle = xlContinuous ‘right is single line[B4].Borders(xlEdgeTop). Linestyle = xlContinuous ‘top is single line[B4].Borders(xlEdgeTop).Weight = xlThick ‘top is thick line[B4].Borders(xlEdgeTop).ColorIndex = 3 ‘top is red[B4].Borders(xlDiagonalDown).Linestyle = xlDash ‘diagonal is dashed[B6:D8].BorderAround xlContinuous, xlThick ‘thick outline[B6:D8].Borders(xlInsideVertical).LineStyle = xlContinuous[B6:D8].Borders(xlInsideHorizontal).LineStyle = xlContinuousExit the VBE. On the Visual Basic toolbar, pick the RUN the program. 3 – Immediate Window There is a window in the VBE called the Immediate Window which offers a cool way to check out a line of code. If you have a single line of code that you want to try, type it into the immediate window and press the enter key. The code will then automatically execute. You can switch back to the worksheet to see the results. In the VBE, you can display the Immediate Window by selecting View – Immediate Window. The figure below shows the Immediate Window:Give it a try:In Immediate Window of the VBE, type the following command and press enter:[C2].Borders(xlEdgeBottom).LineStyle = xlDashDotCheck the result in the worksheet. In the Immediate Window, try each of the other LineStyle options. Check out the worksheet to see what they look like. Note that for each one, you only have to change the option name in the first line you type and then press enter to get the new LineStyle.Immediate WindowCode Window for Module1 in Book14 – Interior Fill The Interior property of a range controls the color shading within the range. When programming color changes to the Interior property of a range, the Color property or the ColorIndex property may be used. The Color property has 8 possible values which are shown in the table. The ColorIndex has 57 possible values numbered 0 through 56 whichcan be seen in section 2 – Borders. Example: Write a line of code to set the activecell fill color to yellow.ActiveCell.Interior.Color = vbYellow– OR –ActiveCell.Interior.ColorIndex = 36Either of these examples would work adequately. The interior of a range can also have a pattern. There are 18 standard patterns that can be assigned to a cell. They are shown in the figure below.Example: Write a line of code to set the activecell fill to the diagonal stripe pattern.ActiveCell.Interior.Pattern = 8Note that the names given in the list above CANNOT be used to assign the pattern value. The color ofthe pattern can be assigned using the PatternColor and PatternColorIndex properties. To make the pattern of the activecell red, either of these lines of code can be used:ActiveCell.Interior.PatternColor = vbRed– OR –ActiveCell.Interior.PatternColorIndex = 3Give it a try:In Module1, create a procedure called CellFormat and include the following commands:[E4:G6].Interior.Pattern = 14[E4:G6].Interior.PatternColor = vbBlue[E4:G6].BorderAround xlContinuous, xlMedium, 5Run the procedure and check the result in the worksheet. What does the 5 in the last line do? _______________________________Color ValueColorSamplevbBlack vbRed vbGreen vbYellow vbBlue vbMagenta vbCyan vbWhite5 – Modifying the Font The font, font size and the color of the text in a cell can be modified using VBA code. The properties are Name, Size, and Color or ColorIndex. Example: Set the font in the activecell to 12 point Arial font. Two lines of code are necessary to perform this task. They are:ActiveCell.Font.Size = 12ActiveCell.Font.Name = “Arial”The first line sets the size while the second line sets the specific font. Note that the font name is specified in quotes. Any standard Window’s font can be specified with the Name property. The size ofthe text is specified in points. Recall that 1 point equals 1/72 of an inch. Either the Color or ColorIndex properties can be