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

Generally, we can use the Alignment group from the Home ribbon to make a center alignment. Besides, while working with Macro, you may need to do the same task with the VBA codes. In this tutorial, we will discuss how to center text and format a cell with Excel VBA.

How to Format Cell and Center Text with Excel VBA: 5 Suitable Ways

As shown in the image below, a sample data set is provided where the products and prices are listed. But no cells are center-aligned. In the following sections, we will apply 5 methods of VBA codes to center text and format them.

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

1.1 Center an Active Cell Horizontally

Look at 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 instructions below.

Step 1: Create a Module

• Firstly, press Alt + F11 to open the VBA Macro.
• Click on Insert.
• Then, select the Module.

Step 2: Write a VBA Code

• Type or paste the following VBA

Sub CenterTextHorizontally()
ActiveCell.HorizontalAlignment = xlCenter
End Sub

Step 3: Run the Program

• Save the program and press F5 to run.
• Therefore, you will see the B4 cell is center-aligned.

1.2 Center Selected Text Horizontally

Step 1: Selection of Cells

• First, select the desired range to select.

Step 2: Type a VBA Code

• Write down the following VBA codes in a new Module.

Sub CenterTextHorizontally()
Selection.HorizontalAlignment = xlCenter
End Sub

Step 3: Get the Result

• Save the program and press F5 to run.
• Finally, your selected range will be center-aligned.

Read More: Excel VBA Horizontal Alignment

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, type the following VBA

Sub VBACenterCellVertical()
ActiveCell.VerticalAlignment = xlCenter
End Sub

Step 2: Save and Run the Program

• Then, save the program and press F5 to run the program.
• Therefore, 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 cells you need.

Step 2: Insert a VBA Code

• After creating a Module, paste the following VBA

Sub VBACenterCellVertical()
Selection.VerticalAlignment = xlCenter
End Sub

• Therefore, you will see that selected cells will be aligned vertically in the center.

Read More: Excel VBA to Set Vertical Alignment

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

In addition, to make a text center-aligned both horizontally and vertically, follow the techniques outlined below.

Step 1: Selection of the cells

• Select the cells in the range you desired.

Step 2: Write a VBA Code

• To make it aligned both horizontally and vertically, write the codes in a new Module.

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

• As a result, the cells in the selection are aligned centrally.

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

You can also format cells with different orientations. It can be a certain angle. Follow the instructions below to get it done.

Step 1: Selection of the cells

• Firstly, select the cells you want.

Step 2: Enter a VBA code

• In a new Module, write 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

• Therefore, cell B4 is rotated 40 degrees clockwise.

Step 3: Repeat the Selection

• Now, repeat the process for the rest blank cells.

Step 4: Enter a VBA code

• To apply the code to a selected cell, write 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

• Finally, by running the program, you will get a similar result.

Read More: Excel VBA to Rotate Text to 90 Degrees

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

Additionally, you can format selected cells by adding Font Color and merging them. To do the task, follow the steps below.

Step 1: Selection of the Cells

• Firstly, select all the cells you need to merge and add color.

Step 2: Paste the VBA Codes

• Then, write or paste the following VBA codes.

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

Step 3: Run the Program

• Finally, save the program and press F5 to run it.
• As a result, your cells will be merged and colored with the RGB color code (128,0,0).

• Apply the codes to the other selections.

• Consequently, get the results you got previously.

Download Practice Workbook

Conclusion

Finally, I hope you now understand how to center text and format cells with Excel VBA. All of these strategies should be carried out when your data is being educated and practiced. Examine the practice book and apply what you’ve learned. We are driven to continue offering programs like this because of your generous support.

If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below.