myITSchools

Excel VBA Font Object

In VBA, there is a font object which you can use to change properties of the font from a cell, like, font color, font size, font type, and you can also apply bold and italic to the font.

Sub font_object()
Range("A1") = "WelCome to myITSchool.com"
Range("A1").font.Name = "Arial"
Range("A1").font.Bold = True
Range("A1").font.Bold = False
Range("A1").font.Size = 20
Range("A1").font.Italic = True
Range("A1").font.Italic = False
Range("A1").font.Underline = True
Range("A1").font.Underline = False
Range("A1").font.Strikethrough = True
Range("A1").font.Strikethrough = False
End Sub

Sub font_color ()
Range("B1") = "VBA"
Range("B1").font.Color = vbRed
Range("B1").font.Color = RGB(23, 54, 189)
Range("B1").font.ColorIndex = 40
End Sub

Cell Background Color

There are two main ways to change the cell background color in VBA that are the Interior.ColorIndex property or the Interior.Color property.

The Interior.ColorIndex property allows you to choose from a predefined color palette of 56 colors. You can use a number from 0 to 56 to specify the color you want. For example, the following code changes the background color of cell A1 to light blue:

Range(“A1”). Interior.ColorIndex = 37

The Interior.Color property allows you to create any color you want using the RGB function. You can use three numbers from 0 to 255 to specify the amount of red, green, and blue in the color. For example, the following code changes the background color of cell A1 to pure red:

Range(“A1”). Interior.Color = RGB (255, 0, 0)

You can use either the Range or the Cells object to refer to the cell you want to change.

Sub Cell_background_color ()
Range("C1") = "Visual Basic For Application"
Range("C1"). Interior.ColorIndex = 40
Cells (3, 3). Interior.ColorIndex = 30
End Sub

Cells Borders

Cell borders are used to enhance the appearance and readability of data in Excel worksheets. You can use VBA to apply different types of borders to a cell or a range of cells. Here are some ways to do that:

  • You can use the Borders property of the Range object to access and modify the borders of a single cell or a portion of a range. For example, the following code changes the color and style of the top border of cell A1:
Range("A1"). Borders(xlEdgeTop). Color = vbRed
Range("A1"). Borders(xlEdgeTop). LineStyle = xlDashDot
  • You can use the BorderAround method of the Range object to apply a border around a cell or a range of cells. For example, the following code adds a thick blue border around the range B2:C6:

Range (“B2:C6”). BorderAround Weight: =xlThick, Color: =vbBlue

  • You can use the Borders.All property of the Range object to set the same border style for all the cells in a range. For example, the following code applies a thin black continuous border to the range D2:E7:
Range ("D2:E7"). Borders.All. LineStyle = xlContinuous
Range ("D2:E7"). Borders.All. Weight = xlThin
Range ("D2:E7"). Borders.All. Color = vbBlack
  • You can also use the Borders(xlInsideHorizontal) and Borders(xlInsideVertical) properties of the Range object to add horizontal and vertical borders inside a range of cells. For example, the following code adds dotted green borders inside the range F2:G8:
Range ("F2:G8"). Borders(xlInsideHorizontal). LineStyle = xlDot
Range ("F2:G8"). Borders(xlInsideHorizontal). Color = vbGreen
Range ("F2:G8"). Borders(xlInsideVertical). LineStyle = xlDot
Range ("F2:G8"). Borders(xlInsideVertical). Color = vbGreen
Sub borders ()
Range("a1:a10").borders.LineStyle = xlDot
Range("c1:c10").borders.LineStyle = xlDash
Range("e1:e10").borders.LineStyle = xlContinous
Range("g1:g10").borders.LineStyle = xlDouble
Range("a1:a10").borders.LineStyle = xlNone
Range("a1:a10").borders.Color = vbGreen
Range("a1:a10").borders.Weight = 3
Range("b1:b10").borders.ColorIndex = 40
Range("b1:b10").borders.Weight = 4
End Sub
Scroll to Top