Format excel cells and border using VBA | debugvba.com

Format Cell VBA Excel

Objective

To format  cells and format the text contained in the cells.

Approach

In this VBA Excel automation, we shall declare a range object and then we shall format the cells and format the text inside the cell .Here we have declared a range variable called “SampleRange” to showcase the use of range object.

Code

Sub FormatExcelRange()

Dim SampleRange As Range
Set SampleRange = Range("A1: B10")

'Formatting the cell borders
With SampleRange.Borders
 .Weight = xlMedium
 .LineStyle = xlDash
 .Color = RGB(0, 206, 255)
End With

'Formatting the cell interior colour
With SampleRange.Interior
.Color = RGB(128, 128, 128)
.TintAndShade = 0.8
End With


'Formatting the text inside cell
With SampleRange.Font
.Bold = True
.Italic = True
.Underline = True
End With

'Change Column Width and row height
With SampleRange
.ColumnWidth = 25
.RowHeight = 20
End With

End Sub

 

What this code does –

  • Created a range object for cell range A1:B10
  • Format the border line thickness, color, line style
  • Change the cell interior color and shade
  • Format the text inside the selected range as bold,italic and underlined
  • Change the column width and row height

Before executing the VBA code, for demonstration purpose we had created some sample data in cell A1:C10 as shown below.

 

Format Cell VBA Excel

 

Once the code is executed completely, cells and the text within cells is formatted.Border is also formatted.

 

 

There are multiple methods and properties which are associated with Range object, they can be used as per requirement, we can find them in the link provided in the reference section.

 

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range(object)

Post you may like

Remove duplicate rows by VBA Excel