Excel VBA, Orientation (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:

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:

VBA Excel, Orientation, Alignment Before

Result:

Orientation,  Excel VBA


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:

VBA Excel, Orientation Before
After:

VBA Excel, Orientation 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:

Excel VBA, Get Orientation
By reducing the lower bound more cells will be colored green:

Excel VBA, Get Orientation, Lower bound modified
By increasing the upper bound more cells will be colored green:

Excel VBA, Get Orientation, Upper bound modified
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.

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website  www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *