Excel VBA, Orientation (Alignment)

Jun 16, 2015 by azurous in Alignment

In this article I will explain how you can change the orientation of the content in cells. I have also provided several sample codes.

Jump To:

Example 1, Basics
Example 2, Set Orientation
Example 3, Get Orientation

You can download the file and code related to this article here.

–

Contents

Example 1, Basics:

The following code applies a 28 deg orientation to the contents of cell A1:

Range("A1").Orientation = 28

Before:

Result:

–

Example 2, Set Orientation:

In this example in row 1 the user selects the orientation to apply to the content of the cells in row 2:

Before:

After:

The program uses a worksheet_change event handler. The event handler executes when the user makes changes to the worksheet:

'executes when the user makes changes to the worksheet Private Sub worksheet_Change(ByVal target As Range) Dim i As Integer For i = 1 To 6 'loops through all the columns Range(Cells(2, i), Cells(2, i)).Orientation = Cells(1, i) Next i End Sub

–

Example 3, Get Orientation:

In this example the user selects a lower and upper bound for the orientation. The program checks the orientation of the content in row 2. If the orientation falls between the upper and lower bounds selected by the user the cell in the next row is colored green:

By reducing the lower bound more cells will be colored green:

By increasing the upper bound more cells will be colored green:

The program uses a worksheet_change event handler. The event handler executes when the user makes changes to the worksheet:

'executes when the user makes changes to the worksheet Private Sub worksheet_Change(ByVal target As Range) Dim i As Integer For i = 1 To 6 'checks if the orientation of the content in the cell under 'consideration is between the upper and lower bounds selected by 'the user If (Range(Cells(2, i), Cells(2, i)).Orientation <= Cells(1, 4)) _ And (Range(Cells(2, i), Cells(2, i)).Orientation >= Cells(1, 2)) Then Range(Cells(3, i), Cells(3, i)).Interior.Color = 3394611 Else Range(Cells(3, i), Cells(3, i)).Interior.Pattern = xlNone End If Next i End Sub

The line below colors the cell green. The number 3394611 is a color code. This was obtained using the macro recorder. For more information about about the macro recorder please see Excel VBA Formatting Cells and Ranges Using the Macro Recorder:

Range(Cells(3, i), Cells(3, i)).Interior.Color = 3394611

The line below removes any fill color previously assigned to the cell:

Range(Cells(3, i), Cells(3, i)).Interior.Pattern = xlNone

You can download the file and code related to this article here.