How to Format Cell and Center Text with Excel VBA (5 Ways)

Method 1 – Run a VBA Code to Center Text Horizontally and Format Cell with Excel VBA

1.1 Center an Active Cell Horizontally

In the image below; there is an active cell B4 that is horizontally center-aligned. To make it center-aligned using the Excel VBA, follow the steps below.

Step 1: Create a Module

Press Alt + F11 to open the VBA Macro.
Click on Insert.
Select Module.

Step 2: Write a VBA Code

Paste the following VBA code.

Sub CenterTextHorizontally()
ActiveCell.HorizontalAlignment = xlCenter
End Sub

Step 3: Run the Program

Save the program and press F5 to run.
The cell B4 is center-aligned.

1.2 Center Selected Text Horizontally

Step 1: Selection of Cells

Select the desired range.

Step 2: Type a VBA Code

Enter the following VBA code in a new Module.

Sub CenterTextHorizontally()
Selection.HorizontalAlignment = xlCenter
End Sub

Step 3: Get the Result

Save the program and press F5 to run.
The selected range will be center-aligned.

Read More: Excel VBA Horizontal Alignment

Method 2 – Perform a VBA Code to Center Text Vertically and Format Cell with Excel VBA

2.1 Center an Active Cell Vertically with Excel VBA

Step 1: Enter a VBA Code

Insert a new Module as before.
In a new Module, enter the following VBA,

Sub VBACenterCellVertical()
ActiveCell.VerticalAlignment = xlCenter
End Sub

Step 2: Save and Run the Program

Save the program and press F5 to run the program.
The active cell B4 will appear vertically center-aligned.

2.2 Center Selected Text Vertically with Excel VBA

Step 1: Selection of Cells

Select all the required cells.

Step 2: Insert a VBA Code

Create a Module and enter the following VBA.

Sub VBACenterCellVertical()
Selection.VerticalAlignment = xlCenter
End Sub

The selected cells will be aligned vertically in the center.

Method 3 – Apply a VBA Code to Center Text Both Horizontally and Vertically with Excel VBA

Step 1: Selection of the cells

Select the required cells in the range.

Step 2: Write a VBA Code

Enter the code in a new Module.

Sub CenterTextHorizontallyAndVertically()
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
End Sub

The cells in the selection are aligned centrally.

Method 4 – Run a VBA Code to Center Text and Format Cell Orientation with Excel VBA

Step 1: Selection of the cells

Select the cells.

Step 2: Enter a VBA code

In a new Module, enter the following VBA,

Sub FormatOrientation()
  With Selection
'Apply command to align horizontally
   .HorizontalAlignment = xlCenter
'Write command to align Vertically
   .VerticalAlignment = xlCenter
'Orientation by clockwise 30 degree
   .Orientation = -30
'Merge the selected cells
   .MergeCells = True
  End With
End Sub

Cell B4 is rotated 40 degrees clockwise.

Step 3: Repeat the Selection

Repeat the process for the remaining blank cells.

Step 4: Enter a VBA code

To apply the code to a selected cell, enter the VBA code in a new Module.

Sub FormatOrientation()
  With Selection
'Apply command to align horizontally
   .HorizontalAlignment = xlCenter
'Write command to align Vertically
   .VerticalAlignment = xlCenter
'Orientation by clockwise 30 degree
   .Orientation = -30
'Merge the selected cells
   .MergeCells = True
  End With
End Sub

Run the program to get the result.

Method 5 – Perform a VBA Code to Center Text and Format Cell Font Color with Excel VBA

Step 1: Selection of the Cells

Select the cells needed to merge and add color.

Step 2: Paste the VBA Codes

Enter the following VBA code.

Sub FormatFont()
  With Selection
'Apply command to align horizontally
   .HorizontalAlignment = xlCenter
'Write command to align Vertically
   .VerticalAlignment = xlCenter   
'Enter RGB value for a font color
   .Font.Color = RGB(128, 0, 0)   
'Orientation by clockwise -45 degree
   .Orientation = -45
'Merge the selected cells
   .MergeCells = True
  End With
End Sub