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.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

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

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

• At 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: How to Format Text in Excel Cell (10 Approaches)

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 as 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: Format Cell as Text (3 Methods)

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.

Read More: Excel VBA Codes to Bold Text in a String (5 Examples)

Similar Readings

• How to Change Lowercase to Uppercase in Excel (6 Methods)
• [Fixed!] Unable to Change Font Color in Excel (3 Solutions)
• How to Convert Text to Time Format with AM/PM in Excel (3 Methods)
• Add Leading Zeros in Excel Text Format (10 Ways)
• How to Format Text to Capitalize First Letter in Excel (10 Ways)

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 make 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 blanks 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: Text Alignment with VBA Command Button in Excel (5 Methods)

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 as you got previously.

Read More: Excel VBA: Change Font Color for Part of Text (3 Methods)

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.

The Exceldemy staff will get back to you as soon as possible.

Stay with us and continue to learn.

Related Articles

• How to Capitalize First Letter of Each Word in Excel (4 Ways)
• Change Lowercase to Uppercase in Excel Without Formula
• How to Change Case in Excel without a Formula (5 Ways)
• Capitalize Each Word in Excel (7 Ways)
• How to Capitalize the First Letter in Excel (3 Methods)
• How to Use VBA Code to Change Font Color in Excel (3 Methods)