How to Format a Cell in Excel with VBA (12 Ways)

Method 1 – Format Cell Horizontally or Vertically with VBA

Excel’s CellFormat. The AddIndent property sets a Variable type value that indicates whether the text is automatically indented when the text alignment in a cell is set horizontally or vertically.

Excel’s CellFormat.AddIndent property to format cell with vba

Sub AddIndentVer()
With Worksheets("AddIndent").Range("C5")
.Orientation = xlVertical
.VerticalAlignment = xlVAlignDistributed
.AddIndent = True
End With
End Sub

In our example, we formatted the cell in vertical alignment. But if you want to align the cell horizontally, then set Orientation = xlHorizontal and VerticalAlignment = xlHAlignDistributed


Method 2 – Format Alignment of Text in a Cell with Macro

 

2.1. Horizontal Alignment

If you want to align the text of a cell horizontally, then the VBA code is,

Format Horizontal alignment of Text in a Cell with VBA Macro

Sub Horizontal()
Range("C5").HorizontalAlignment = xlRight
End Sub

You can set the value of the property to any of the following constants according to your requirements,

  • xlGeneral
  • xlCenter
  • xlDistributed
  • xlJustify
  • xlLeft
  • xlRight.

2.2. Vertical Alignment

If you want to align the text of a cell vertically, then the VBA code is,

Format vertical alignment of Text in a Cell with VBA Macro

Sub Vertical()
Range("C5").VerticalAlignment = xlBottom
End Sub

You can set the value of the property to any of the following constants according to your needs,

  • xlBottom
  • xlCenter
  • xlDistributed
  • xlJustify
  • xlTop.

Method 3 – Format Borders of a Cell with VBA Code

You can format the borders of a cell according to your needs. The VBA code for this is,

Format Borders of a Cell with VBA Code

Sub Border()
    Worksheets("Border").Range("C5").BorderAround LineStyle:=xlDash, ColorIndex:=5
End Sub

Border Index

You can choose different borders for a single cell or cells of a range according to your needs using VBA.

  • xlDiagonalDown: Border running from the upper left-hand corner to the lower right of each cell in the range.
  • xlDiagonalUp: Border running from the lower left-hand corner to the upper right of each cell in the range.
  • xlEdgeBottom: Border at the bottom of the range.
  • xlEdgeLeft: Border at the left-hand edge of the range.
  • xlEdgeRight: Border at the right-hand edge of the range.
  • xlEdgeTop: Border at the top of the range.
  • xlInsideHorizontal: Horizontal borders for all cells except borders on the outside of the range.
  • xlInsideVertical: Vertical borders are for all the cells in the range except for the borders outside of the range.

Method 4 – Format the Font of a Cell with VBA in Excel

You can format a cell’s name, style, size, color, effects, underlines, etc., using the VBA macro in the Excel worksheet.

The VBA code for that is,

Format Font of a Cell with VBA in Excel

Sub Font()
    With Range("C5:C7").Font
    .Name = "Century"
    .FontStyle = "Bold"
    .Size = 14
    .Strikethrough = True
    .Subscript = False
    .Superscript = True
    End With
End Sub 


Method 5 – Hide Cells of a Range in Excel with VBA

The FormulaHidden property sets a Variant type of value that indicates if the formula will be hidden when the worksheet is protected. The macro to do this is,

Sub HiddenFormula()
   Worksheets("Sheet7").Range("B4:B6").FormulaHidden = True
End Sub

Format Hidden Cell of a Range in Excel with VBA


Method 6 – Change Indent Level for a Cell in Excel

IndentLevel sets an integer value between 0 and 15, representing the cell’s indent level or range.

The VBA code to format the indent level for a cell is,

Format Indent Level for a Cell in Excel VBA

Sub IndentLevel()
    Worksheets("Indent").Range("C5").IndentLevel = 7
End Sub


Method 7 – Change the Interior of a Cell in Excel

VBA code can set the interior of a cell, such as Color, Color Index, Pattern, Pattern Color, Pattern Color Index, Pattern Theme Color, Pattern Tint and Shade, Theme Color, Tint and Shade, etc.

Format the Interior of a Cell in Excel VBA

Sub Interior()
If Not Range("C5").Interior.ThemeColor = ThemeColorLight2 Then
   Range("C5").Interior.Pattern = xlPatternUp
End If
End Sub


Method 8 – Excel’s Locked Property for Cells

This property returns True if the cell or range is locked and False if the value can be modified when the sheet is protected. When the specified range contains locked and unlocked cells, it returns Null. This property can also be used to lock or unlock cells.

The following VBA code locks cell B4:C4 on the Excel sheet “Locked” so that they can not be modified when the sheet is protected.

Sub LockCell()
    Worksheets("Locked").Range("B4:C4").Locked = False
    Worksheets("Locked").Protect
End Sub

Method 9 – Merge Cells in Excel with Macro

If you want to merge cells in your Excel worksheet, set this property to True.

VBA macro to merge cells B8:D8 is,

Format Merge Cell in Excel with VBA

Sub Merged()
    Worksheets("Merged").Range("B8:D8").MergeCells = True
End Sub


Method 10 – Format the Orientation of a Cell in Excel

Text orientation within the cell(s) can be set or returned by this property.

Format the Orientation of a Cell in Excel VBA

Sub Orientation()
    Worksheets("Orientation").Range("C5").Orientation = -90
End Sub

Its value can be any of the constants mentioned here,

  • xlDownward
  • xlHorizontal
  • xlUpward
  • xlVertical or
  • an integer value from -90 to 90 degrees.

Method 11 – Format a Cell to Shrink to Fit in Excel

Excel’s ShrinkToFit property sets or returns a Variant type value to shrink automatically to fit the specified column width.

Format a Cell to Shrink to Fit in Excel VBA

Sub Shrink()
    Worksheets("Shrink").Range("C5").ShrinkToFit = True
End Sub


Method 12 – WrapText Property to Format Cell in Excel

This property returns True if the text is wrapped in cells within a specified range. It returns False if the text is not wrapped in all cells and Null if the specified range contains some wrapped texts and some unwrapped cells.

WrapText Property to Format Cell in Excel VBA

Sub WrapText()
    Worksheets("Wrapped").Range("A1:B1").WrapText
End Sub

For Instance, the following example will return Null in the Immediate window.


Download the Practice Workbook

You can download the free practice Excel workbook from here.

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo