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.

Sample Data

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.

Suitable Ways to Center Text and Format Cell with Excel VBA

Step 1: Create a Module

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

Suitable Ways to Center Text and Format Cell with Excel VBA

Step 2: Write a VBA Code

  • Type or paste the following VBA
Sub CenterTextHorizontally()
ActiveCell.HorizontalAlignment = xlCenter
End Sub

Suitable Ways to Center Text and Format Cell with Excel VBA

Step 3: Run the Program

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

Suitable Ways to Center Text and Format Cell with Excel VBA

1.2 Center Selected Text Horizontally

Step 1: Selection of Cells

  • At first, select the desired range to select.

Suitable Ways to Center Text and Format Cell with Excel VBA

Step 2: Type a VBA Code

  • Write down the following VBA codes in a new Module.
Sub CenterTextHorizontally()
Selection.HorizontalAlignment = xlCenter
End Sub

Suitable Ways to Center Text and Format Cell with Excel VBA

Step 3: Get the Result

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

Suitable Ways to Center Text and Format Cell with Excel VBA

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

Suitable Ways to Center Text and Format Cell with Excel VBA

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.

Suitable Ways to Center Text and Format Cell with Excel VBA

2.2 Center Selected Text Vertically with Excel VBA

Step 1: Selection of Cells

  • Select all the cells you need.

Suitable Ways to Center Text and Format Cell with Excel VBA

Step 2: Insert a VBA Code

  • After creating a Module, paste the following VBA
Sub VBACenterCellVertical()
Selection.VerticalAlignment = xlCenter
End Sub

Suitable Ways to Center Text and Format Cell with Excel VBA

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

Suitable Ways to Center Text and Format Cell with Excel VBA

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.

Suitable Ways to Center Text and Format Cell with Excel VBA

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

Suitable Ways to Center Text and Format Cell with Excel VBA

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

Suitable Ways to Center Text and Format Cell with Excel VBA

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


Similar Readings


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.

Suitable Ways to Center Text and Format Cell with Excel VBA

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

Suitable Ways to Center Text and Format Cell with Excel VBA

  • Therefore, cell B4 is rotated 40 degrees clockwise.

Suitable Ways to Center Text and Format Cell with Excel VBA

Step 3: Repeat the Selection

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

Sample Data

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

Suitable Ways to Center Text and Format Cell with Excel VBA

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

Sample Data

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.

Sample Data

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

Sample Data

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).

Sample Data

  • Apply the codes to the other selections.

Sample Data

  • Consequently, get the results as you got previously.

Sample Data

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

Bhubon Costa

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo