Excel VBA, Get Vertical Alignment (Sample Code)

In this article I will provide an example on how to find cells with a specific vertical alignment. You can download the code and file related to this article here.

The user selects the desired vertical alignment to find from a drop down list:

Excel VBA, Set Vertical Alignment
After pressing the run button the program searches row 3 for the alignment specified by the drop down list. If the alignment formatting is matched, the cell below the text is colored green:

Excel VBA, Set Vertical Alignment, Center Alignment
By modifying the cell alignments in row 3 and pressing run the program repeats the search:

Excel VBA, Set Vertical Alignment, Modified Cells
A drop down list is created using data validation. The input values for the drop down list are in column A of sheet2. For more information about creating drop down lists please see Excel VBA Drop Down Lists:

VBA Excel, Vertical Alignment, Drop Down List Values

The program has 2 main functions. The first function is executed when the run button is pressed. It loops through all the cells in row 3 and checks for an alignment match. If a match is found the adjacent cell is colored green:

'executes when the user presses the Run button
Private Sub btnRun_Click()
Dim i As Integer
'loops through all the columns
For i = 1 To 6
'checks if the alignment matches the alignment selected from
'the drop down list
If CheckAlignment(Cells(1, 3), i) = True Then
Range(Cells(4, i), Cells(4, i)).Interior.Color = 3394611
Else
Range(Cells(4, i), Cells(4, 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(4, i), Cells(4, i)).Interior.Color = 3394611

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

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

The function CheckAlignment() receives as input 2 parameters:

  1. strAlignment: The alignment selected from the drop down list to be matched with the alignments in row 3.
  2. intColumn: The column index to check for the match.

It checks if the cell in row 3 in the column specified by the parameter intColumn has the alignment specified by the parameter strAlignment and returns True if a match is made and False if not:

'checks if the selected alignment from the drop down list matches
'the alignment of the cell in row 3 and the column specified by
'the intColumn input parameter
Private Function CheckAlignment(ByVal strAlignment As String, ByVal intColumn _
As Integer) As Boolean
'apply top alignment

'Check center alignment
If strAlignment = "Center" Then
If Range(Cells(3, intColumn), Cells(3, intColumn)).VerticalAlignment _
= xlCenter Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check left alignment
ElseIf strAlignment = "Top" Then
If Range(Cells(3, intColumn), Cells(3, intColumn)).VerticalAlignment _
= xlTop Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check right alignment
ElseIf strAlignment = "Bottom" Then
If Range(Cells(3, intColumn), Cells(3, intColumn)).VerticalAlignment _
= xlBottom Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check justify alignment
ElseIf strAlignment = "Justify" Then
If Range(Cells(3, intColumn), Cells(3, intColumn)).VerticalAlignment _
= xlJustify Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check distributed alignment
ElseIf strAlignment = "Distributed" Then
If Range(Cells(3, intColumn), Cells(3, intColumn)).VerticalAlignment _
= xlDistributed Then
CheckAlignment = True
Else
CheckAlignment = False
End If
End If
End Function

Results:

User selects bottom alignment:

Excel VBA, Set Vertical Alignment, Bottom Alignment
User selects justify alignment:

Excel VBA, Set Vertical Alignment, Justify Alignment

User selects distributed alignment:

Excel VBA, Set Vertical Alignment, Distributed Alignment

You can download the code and file 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 *