Formatting Cells Alignment with VBA

Text Alignment

Horizontal

The value of HorizontalAlignment property can be set to one of the XlHAlign constants:

Name	Value	Description
xlHAlignCenter	-4108	Center.
xlHAlignCenterAcrossSelection	7	Center across selection.
xlHAlignDistributed	-4117	Distribute.
xlHAlignFill	5	Fill.
xlHAlignGeneral	1	Align according to data type.
xlHAlignJustify	-4130	Justify.
xlHAlignLeft	-4131	Left.
xlHAlignRight	-4152	Right.

The following code sets the horizontal alignment of cell A1 to center.

Range("A1").HorizontalAlignment = xlHAlignCenter

Justify Distributed

The following code sets the horizontal alignment for text in cell A1 on Sheet1 to equal distribution and then indents the text.

With Worksheets("Sheet1").Range("A1")
    .HorizontalAlignment = xlHAlignDistributed
    .AddIndent = True
End With

Indent

The following code sets the indent level to 4 in cell A1. Can be an integer from 0 to 15.

With Range("A1")
    .IndentLevel = 4
End With

Vertical

The value of VerticalAlignment property can be set to one of the XlVAlign constants.

Name	Value	Description
xlVAlignBottom	-4107	Bottom
xlVAlignCenter	-4108	Center
xlVAlignDistributed	-4117	Distributed
xlVAlignJustify	-4130	Justify
xlVAlignTop	-4160	Top

The following code sets the vertical alignment of cell A1 to bottom.

Range("A1").VerticalAlignment = xlVAlignBottom

Text Control

Wrap Text

This example use WrapText property formats cell A1 so that the text wraps within the cell.

Range("A1").WrapText = True

Shrink To Fit

This example causes text in row one to automatically shrink to fit in the available column width use ShrinkToFit property.

Rows(1).ShrinkToFit = True

Merge Cells

This example merge range A1:A4 to a large one.

Range("A1:A4").MergeCells = True

Right-to-left

Text direction

The value of ReadingOrder property can be set to one of the XlReadingOrder constants:

Name	Value	Description
xlContext	-5002	According to context.
xlLTR	-5003	Left-to-right.
xlRTL	-5004	Right-to-left.

The following code example sets the reading order of cell A1 to xlRTL (right-to-left).

Range("A1").ReadingOrder = xlRTL

Orientation

The value of Orientation property can be set to an integer value from –90 to 90 degrees or to one of the XlOrientation constants:

Name	Value	Description
xlDownward	-4170	Text runs downward.
xlHorizontal	-4128	Text runs horizontally.
xlUpward	-4171	Text runs upward.
xlVertical	-4166	Text runs downward and is centered in the cell.

The following code example sets the orientation of cell A1 to xlHorizontal.

Range("A1").Orientation = xlHorizontal