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