Home

Cells Borders

 

Introduction

As we have mentioned already, a cell appears and behaves like a rectangular box. As such, it has borders and a background. Microsoft Excel provides a default appearance for a cell with regards to its background. For example, it surrounds the cell with a gray border and a white background. You can keep these defaults or you can change them as you see fit.

 

The Line Style of a Border

To visually control the borders of a cell or a group of cells, on the Ribbon, click Home. In the Font section, click the arrow of the Borders button and select one of the options:

Border

 

To programmatically control the borders of a cell or a group of cells, refer to the cell or the group of cells and access its Borders object. This object is accessed as an indexed property. Here is an example:

Range("B2").Borders()

In the parentheses of the Borders property, specify the border you want to change. The primary available values are: xlEdgeBottom, xlEdgeTop, xlEdgeLeft, and xlEdgeRight. Sometimes you may have selected a group of cells and you want to take an action on the line(s) between (among) them. To support this, the Borders property can take an index named xlInsideVertical for a vertical border between two cells or an index named xlInsideHorizontal for a horizontal border between the cells.

After specifying the border you want to work on, you must specify the type of characteristic you want to change. For example, you can specify the type of line you want the border to show. To support this, the Borders object is equipped with a property named LineStyle. To specify the type of line you want the border to display, you can assign a value to the LineStyle property. The available values are xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlSlantDashDot, and xlLineStyleNone. Therefore, you can assign any of these values to the property. To assist you with this, you can type LineStyle followed by a period and select the desired value from the list that appears:

Practical LearningPractical Learning: Specifying the Styles of Cells Border

  • Change the code as follows:
     
    Sub CreateWorkbook()
        ' CreateWorkbook Macro
        ' This macro is used to create a workbook for the
        ' Georgetown Dry Cleaning Services
    
        ' Keyboard Shortcut: Ctrl+Shift+W
        
        Rem Just in case there is anything on the
        Rem worksheet, delete everything
        Range("A:K").Delete
        Range("1:20").Delete
        
        Rem Create the sections and headings of the worksheet
        Range("B2") = "Georgetown Dry Cleaning Services"
        Range("B2").Font.Name = "Rockwell Condensed"
        Range("B2").Font.Size = 24
        Range("B2").Font.Bold = True
        Range("B2").Font.Color = vbBlue
        
        Range("B5") = "Order Identification"
        Range("B5").Font.Name = "Cambria"
        Range("B5").Font.Size = 14
        Range("B5").Font.Bold = True
        Range("B5").Font.ThemeColor = 5
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("B6") = "Receipt #:"
        Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("G6") = "Order Status:"
        Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("B7") = "Customer Name:"
        Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("G7") = "Customer Phone:"
        Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("B9") = "Date Left:"
        Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("G9") = "Time Left:"
        Range("I9:J9").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("B10") = "Date Expected:"
        Range("D10:F10").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("G10") = "Time Expected:"
        Range("I10:J10").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("B11") = "Date Picked Up:"
        Range("D11:F11").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("G11") = "Time Picked Up:"
        Range("I11:J11").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous
              
        Range("B13") = "Items to Clean"
        Range("B13").Font.Name = "Cambria"
        Range("B13").Font.Size = 14
        Range("B13").Font.Bold = True
        
        Range("B14") = "Item"
        Range("D14") = "Unit Price"
        Range("E14") = "Qty"
        Range("F14") = "Sub-Total"
        
        Range("B14:F14").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeTop).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("C14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E14").Borders(xlEdgeRight).LineStyle = xlContinuous
    
        Range("B15") = "Shirts"
        Range("B15").Borders(xlEdgeLeft).LineStyle = xlContinuous
        
        Range("H15") = "Order Summary"
        Range("H15").Font.Name = "Cambria"
        Range("H15").Font.Size = 14
        Range("H15").Font.Bold = True
        
        Range("B16") = "Pants"
        Range("B16").Borders(xlEdgeLeft).LineStyle = xlContinuous
        
        Range("B17") = "None"
        Range("B17").Borders(xlEdgeLeft).LineStyle = xlContinuous
        
        Range("H17") = "Cleaning Total:"
        Range("I17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("B18") = "None"
        Range("B18").Borders(xlEdgeLeft).LineStyle = xlContinuous
        
        Range("H18") = "Tax Rate:"
        Range("I18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("I18") = "5.75"
        Range("J18") = "%"
        Range("B19") = "None"
        Range("B19").Borders(xlEdgeLeft).LineStyle = xlContinuous
        
        Range("H19") = "Tax Amount:"
        Range("I19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("B20") = "None"
        Range("B20").Borders(xlEdgeLeft).LineStyle = xlContinuous
        
        Range("C15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("B14:C14").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Range("B15:C15").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D15:F15").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F15").Borders(xlEdgeRight).LineStyle = xlContinuous
        
        Range("B16:C16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D16:F16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F16").Borders(xlEdgeRight).LineStyle = xlContinuous
        
        Range("B17:C17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D17:F17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F17").Borders(xlEdgeRight).LineStyle = xlContinuous
        
        Range("B18:C18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D18:F18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F18").Borders(xlEdgeRight).LineStyle = xlContinuous
        
        Range("B19:C19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D19:F19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F19").Borders(xlEdgeRight).LineStyle = xlContinuous
        
        Range("B20:F20").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F20").Borders(xlEdgeRight).LineStyle = xlContinuous
        
        Range("H20") = "Order Total:"
        Range("I20").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Rem Change the widths and heights of some columns and rows
        Rem In previous lessons, we learned all these things
        Range("E:E, G:G").ColumnWidth = 4
        Columns("H").ColumnWidth = 14
        Columns("J").ColumnWidth = 1.75
        
        Rows("3").RowHeight = 2
        Range("8:8, 12:12").RowHeight = 8
        
        Rem Merge the cells H15, I15, H16, and I16
        Range("H15:I16").MergeCells = True
        Rem Align the merged text to the left
        Range("H15:H16").VerticalAlignment = xlCenter
        
        Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        Rem Hide the gridlines
        ActiveWindow.DisplayGridlines = False
    End Sub
 
 

The Weight of a Border

After specifying the type of line to apply to a border, you can control the thickness of the line. To support this, the Borders object is equipped with a property named Weight. The available values are xlHairline, xlThin, xlMedium, and xlThick.

Practical LearningPractical Learning: Specifying the Weight of Cells Border

  1. Change the code as follows:
     
    Sub CreateWorkbook()
        ' CreateWorkbook Macro
        ' This macro is used to create a workbook for the
        ' Georgetown Dry Cleaning Services
    
        ' Keyboard Shortcut: Ctrl+Shift+W
        
        Rem Just in case there is anything on the
        Rem worksheet, delete everything
        Range("A:K").Delete
        Range("1:20").Delete
        
        Rem Create the sections and headings of the worksheet
        Range("B2") = "Georgetown Dry Cleaning Services"
        Range("B2").Font.Name = "Rockwell Condensed"
        Range("B2").Font.Size = 24
        Range("B2").Font.Bold = True
        Range("B2").Font.Color = vbBlue
        
        Range("B5") = "Order Identification"
        Range("B5").Font.Name = "Cambria"
        Range("B5").Font.Size = 14
        Range("B5").Font.Bold = True
        Range("B5").Font.ThemeColor = 5
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium
        
        Range("B6") = "Receipt #:"
        Range("D6:F6").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D6:F6").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G6") = "Order Status:"
        Range("I6:J6").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I6:J6").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B7") = "Customer Name:"
        Range("D7:F7").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D7:F7").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G7") = "Customer Phone:"
        Range("I7:J7").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I7:J7").Borders(xlEdgeBottom).Weight = xlHairline
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B8:J8").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B8:J8").Borders(xlEdgeBottom).Weight = xlThin
        
        Range("B9") = "Date Left:"
        Range("D9:F9").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D9:F9").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G9") = "Time Left:"
        Range("I9:J9").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I9:J9").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B10") = "Date Expected:"
        Range("D10:F10").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D10:F10").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G10") = "Time Expected:"
        Range("I10:J10").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I10:J10").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B11") = "Date Picked Up:"
        Range("D11:F11").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D11:F11").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("G11") = "Time Picked Up:"
        Range("I11:J11").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I11:J11").Borders(xlEdgeBottom).Weight = xlHairline
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B12:J12").Borders(xlEdgeBottom).Weight = xlMedium
              
        Range("B13") = "Items to Clean"
        Range("B13").Font.Name = "Cambria"
        Range("B13").Font.Size = 14
        Range("B13").Font.Bold = True
        
        Range("B14") = "Item"
        Range("D14") = "Unit Price"
        Range("E14") = "Qty"
        Range("F14") = "Sub-Total"
        
        Range("B14:F14").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeLeft).Weight = xlThin
        Range("B14:F14").Borders(xlEdgeTop).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeTop).Weight = xlThin
        Range("B14:F14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeRight).Weight = xlThin
        Range("B14:F14").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B14:F14").Borders(xlEdgeBottom).Weight = xlThin
        Range("C14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C14").Borders(xlEdgeRight).Weight = xlThin
        Range("D14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D14").Borders(xlEdgeRight).Weight = xlThin
        Range("E14").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E14").Borders(xlEdgeRight).Weight = xlThin
    
        Range("B15") = "Shirts"
        Range("B15").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B15").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("H15") = "Order Summary"
        Range("H15").Font.Name = "Cambria"
        Range("H15").Font.Size = 14
        Range("H15").Font.Bold = True
        
        Range("B16") = "Pants"
        Range("B16").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B16").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("B17") = "None"
        Range("B17").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B17").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("H17") = "Cleaning Total:"
        Range("I17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I17").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B18") = "None"
        Range("B18").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B18").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("H18") = "Tax Rate:"
        Range("I18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I18").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("I18") = "5.75"
        Range("J18") = "%"
        Range("B19") = "None"
        Range("B19").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B19").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("H19") = "Tax Amount:"
        Range("I19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I19").Borders(xlEdgeBottom).Weight = xlHairline
        
        Range("B20") = "None"
        Range("B20").Borders(xlEdgeLeft).LineStyle = xlContinuous
        Range("B20").Borders(xlEdgeLeft).Weight = xlThin
        
        Range("C15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C15").Borders(xlEdgeRight).Weight = xlThin
        Range("C16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C16").Borders(xlEdgeRight).Weight = xlThin
        Range("C17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C17").Borders(xlEdgeRight).Weight = xlThin
        Range("C18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C18").Borders(xlEdgeRight).Weight = xlThin
        Range("C19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C19").Borders(xlEdgeRight).Weight = xlThin
        Range("C20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("C20").Borders(xlEdgeRight).Weight = xlThin
        Range("B14:C14").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B14:C14").Borders(xlEdgeBottom).Weight = xlThin
        
        Range("B15:C15").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B15:C15").Borders(xlEdgeBottom).Weight = xlThin
        Range("D15:F15").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D15:F15").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D15").Borders(xlEdgeRight).Weight = xlHairline
        Range("E15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E15").Borders(xlEdgeRight).Weight = xlHairline
        Range("F15").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F15").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B16:C16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B16:C16").Borders(xlEdgeBottom).Weight = xlThin
        Range("D16:F16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D16:F16").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D16").Borders(xlEdgeRight).Weight = xlHairline
        Range("E16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E16").Borders(xlEdgeRight).Weight = xlHairline
        Range("F16").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F16").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B17:C17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B17:C17").Borders(xlEdgeBottom).Weight = xlThin
        Range("D17:F17").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D17:F17").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D17").Borders(xlEdgeRight).Weight = xlHairline
        Range("E17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E17").Borders(xlEdgeRight).Weight = xlHairline
        Range("F17").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F17").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B18:C18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B18:C18").Borders(xlEdgeBottom).Weight = xlThin
        Range("D18:F18").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D18:F18").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D18").Borders(xlEdgeRight).Weight = xlHairline
        Range("E18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E18").Borders(xlEdgeRight).Weight = xlHairline
        Range("F18").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F18").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B19:C19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B19:C19").Borders(xlEdgeBottom).Weight = xlThin
        Range("D19:F19").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("D19:F19").Borders(xlEdgeBottom).Weight = xlHairline
        Range("D19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D19").Borders(xlEdgeRight).Weight = xlHairline
        Range("E19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E19").Borders(xlEdgeRight).Weight = xlHairline
        Range("F19").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F19").Borders(xlEdgeRight).Weight = xlThin
        
        Range("B20:F20").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B20:F20").Borders(xlEdgeBottom).Weight = xlThin
        Range("D20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("D20").Borders(xlEdgeRight).Weight = xlHairline
        Range("E20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("E20").Borders(xlEdgeRight).Weight = xlHairline
        Range("F20").Borders(xlEdgeRight).LineStyle = xlContinuous
        Range("F20").Borders(xlEdgeRight).Weight = xlThin
        
        Range("H20") = "Order Total:"
        Range("I20").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("I20").Borders(xlEdgeBottom).Weight = xlHairline
        
        Rem Change the widths and heights of some columns and rows
        Rem In previous lessons, we learned all these things
        Range("E:E, G:G").ColumnWidth = 4
        Columns("H").ColumnWidth = 14
        Columns("J").ColumnWidth = 1.75
        
        Rows("3").RowHeight = 2
        Range("8:8, 12:12").RowHeight = 8
        
        Rem Merge the cells H15, I15, H16, and I16
        Range("H15:I16").MergeCells = True
        Rem Align the merged text to the left
        Range("H15:H16").VerticalAlignment = xlCenter
        
        Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("H16:I16").Borders(xlEdgeBottom).Weight = xlMedium
        
        Rem Hide the gridlines
        ActiveWindow.DisplayGridlines = False
    End Sub
  2. Return to Microsoft Excel and press Ctrl + Shift + W to see the result
     
    Georgetown Dry Cleaning Services
  3. Return to Microsoft Visual Basic

The Color of a Border

Besides its type of line, a border can be painted with a color of your choice and you have many options.

To visually specify the color of a border, you can right-click a cell or a group of selected cells and click Format Cells... In the next section, we will review the Format Cells dialog box.

To programmatically specify the color of a border, access the Borders indexed property of a cell or a group of cells and specify the border whose color you want to change, as we saw in the previous section. To support colors, the Borders object is equipped with a property named Color. To specify the color, assign the desired color to the property. The VBA provides a (limited) list of colors such as vbBlack, vbWhite, vbRed, vbGreen, and vbBlue. In reality, a color in Microsoft Windows is represented as a color between 0 and 16,581,375.

Practical LearningPractical Learning: Controlling the Colors of Cells Borders

  1. Change the code as follows:
     
    Sub CreateWorkbook()
        ' CreateWorkbook Macro
        ' This macro is used to create a workbook for the
        ' Georgetown Dry Cleaning Services
    
        ' Keyboard Shortcut: Ctrl+Shift+W
        
        . . . No Change
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B5:J5").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B5:J5").Borders(xlEdgeBottom).Weight = xlMedium
        Range("B5:J5").Borders(xlEdgeBottom).ThemeColor = 5
        
        . . . No Change
        
        Rem To draw a thick line, change the bottom
        Rem borders of the cells from B5 to J5
        Range("B12:J12").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("B12:J12").Borders(xlEdgeBottom).Weight = xlMedium
        Range("B12:J12").Borders(xlEdgeBottom).ThemeColor = 5
              
        . . . No Change
        
        Range("H16").Borders(xlEdgeBottom).LineStyle = xlContinuous
        Range("H16:I16").Borders(xlEdgeBottom).Weight = xlMedium
        Range("H16:I16").Borders(xlEdgeBottom).ThemeColor = 5
        
        Rem Hide the gridlines
        ActiveWindow.DisplayGridlines = False
    End Sub
  2. Return to Microsoft Excel and press Ctrl + Shift + W to see the result
  3. Return to Microsoft Visual Basic

The Border Property Page

To better visually control the borders of a cell or a group of cells, you use the Border property page of the Format Cells dialog box. To access it:

  • On the Ribbon, click Home. In the Font section, the Alignment section, or the Number section, click the more options button
  • Right-click the cell or the group of selected cells and click Format Cells...

Any of these actions would display the Format Cells dialog box where you would click the Border tab.

 
 

 

 

Previous Copyright © 2008-2010 FunctionX, Inc. Next