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:
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:
By modifying the cell alignments in row 3 and pressing run the program repeats the search:
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:
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:
- strAlignment: The alignment selected from the drop down list to be matched with the alignments in row 3.
- 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:
User selects justify alignment:
User selects distributed alignment:
You can download the code and file related to this article here.
See also:
- Excel VBA Set Vertical Alignment (Sample Code)
- Excel VBA, Vertical Alignment
- Excel VBA, Alignment
- Excel VBA Formatting Cells and Ranges Using the Macro Recorder
- Excel VBA Drop Down Lists
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