In this article, we will show you how to format a cell in numerous ways in Excel with VBA.
You can download the free practice Excel workbook from here.
12 Ways to Format Cell in Excel with VBA Macro
In this phase, you will learn 12 effective ways of VBA code to format a cell in Excel.
1. Format Cell Horizontally or Vertically with VBA
Excel’s CellFormat.AddIndent property sets a Variant type value that indicates if a text is automatically indented when the text alignment in a cell is set either horizontally or vertically.
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
Read More: How to Format Cell Based on Formula in Excel (13 Examples)
2. Format Alignment of Text in a Cell with Macro
You can format text alignment inside a cell with the VBA macro.
2.1. Horizontal Alignment
If you want to align the text of a cell horizontally then the VBA code is,
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 requirement,
2.2. Vertical Alignment
If you want to align the text of a cell vertically then the VBA code is,
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 need,
Read More: How to Format Text Using Excel VBA
3. Format Borders of a Cell with VBA Code
You can format the borders of a cell according to your need. The VBA code for this is,
Sub Border() Worksheets("Border").Range("C5").BorderAround LineStyle:=xlDash, ColorIndex:=5 End Sub
You can choose different borders for a single cell or cells of a range according to your need 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 in the range except borders on the outside of the range.
- xlInsideVertical: Vertical borders for all the cells in the range except borders on the outside of the range.
Read More: How to Add or Remove Cell Borders in Excel
4. Format Font of a Cell with VBA in Excel
You can format a cell’s name, style, size, colour, effects, underlines etc. using VBA macro in Excel worksheet.
The VBA code for that is,
Sub Font() With Range("C5:C7").Font .Name = "Century" .FontStyle = "Bold" .Size = 14 .Strikethrough = True .Subscript = False .Superscript = True End With End Sub
Read More: Change the color of text and background of cells – An Excel Font and Fill Color Complete Guide
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
Read More: How to Use Excel Cell Format Formula (4 Effective Methods)
6. Change Indent Level for a Cell in Excel
IndentLevel sets an integer value between 0 to 15 that represents the indent level for the cell or a range.
The VBA code to format the indent level for a cell is,
Sub IndentLevel() Worksheets("Indent").Range("C5").IndentLevel = 7 End Sub
Read More: How to Change Time Format in Excel (4 Ways)
- Uses of CELL Color A1 in Excel (3 Examples)
- How to Copy Cell Format in Excel (4 Methods)
- Use Format Painter in Excel (7 Ways)
- How to Copy Formatting in Excel (3 Easy ways)
7. Change the Interior of a Cell in Excel
A cell’s interior such as Color, ColorIndex, Pattern, PatternColor, PatternColorIndex, PatternThemeColor, PatternTintAndShade, ThemeColor, TintAndShade etc. can be set with VBA code.
Sub Interior() If Not Range("C5").Interior.ThemeColor = ThemeColorLight2 Then Range("C5").Interior.Pattern = xlPatternUp End If End Sub
8. Excel’s Locked Property for Cells
This property returns True if the cell or range is locked, returns False if the value can be modified when the sheet is protected. Or when the specified range contains both locked and unlocked cells, it returns Null. This property also can be used for locking or unlocking cells.
The following VBA code locks cell B4:C4 on 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
9. Merge Cells in Excel with Macro
If you want to merge cells in your Excel worksheet then set this property to True.
VBA macro to merge cells B8:D8 is,
Sub Merged() Worksheets("Merged").Range("B8:D8").MergeCells = True End Sub
10. Format the Orientation of a Cell in Excel
Text orientation within the cell(s) can be set or returned by this property.
Sub Orientation() Worksheets("Orientation").Range("C5").Orientation = -90 End Sub
Its value can be any of the constants mentioned here,
- xlVertical or
- an integer value from -90 to 90 degrees.
11. Format a Cell to Shrink to Fit in Excel
Excel’s ShrinkToFit property sets or returns a Variant type value to automatically shrink to fit in the specified column width.
Sub Shrink() Worksheets("Shrink").Range("C5").ShrinkToFit = True End Sub
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 returns Null if the specified range contains some wrapped texts and some unwrapped cells.
Sub WrapText() Worksheets("Wrapped").Range("A1:B1").WrapText End Sub
For Instance, the following example will return Null in the Immediate window.
Read More: Formula to Copy Cell Value and Format in Excel (5 Uses)
This article showed you how to format a cell or cells in Excel with VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.