Excel VBA, Get Horizontal Alignment (Sample Code)
In this article I will provide an example on how to find cells with a specific horizontal alignment. You can download the code and file related to this article here.
The user selects the desired horizontal alignment to find from a drop down list:
After pressing the run button the program searches column A for the alignment specified by the drop down list. If the alignment formatting is matched, the adjacent cell is colored green:
By modifying the cell alignments in column A 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 column A and checks for an alignment match. If a match is found the adjacent cell is colored green:
'Executes when the run button is pressed
Private Sub btnRun_Click()
Dim i As Integer
'loops through the cells in columnA
For i = 3 To 13
'checks if the alignment matches the alignment selected from
'the drop down list
If CheckAlignment(Cells(1, 2), i) = True Then
Range(Cells(i, 2), Cells(i, 2)).Interior.Color = 3394611
Else
Range(Cells(i, 2), Cells(i, 2)).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(i, 2), Cells(i, 2)).Interior.Color = 3394611
The line below removes any fill color previously assigned to the cell:
Range(Cells(i, 2), Cells(i, 2)).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 column A.
- intRow: The row index to check for the match.
It checks if the cell in Column A in the row specified by the parameter intRow 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 column A and the row specified by
'the intRow input parameter
Private Function CheckAlignment(ByVal strAlignment As String, ByVal _
intRow As Integer) As Boolean
'Check center alignment
If strAlignment = "Center" Then
If Range(Cells(intRow, 1), Cells(intRow, 1)).HorizontalAlignment _
= xlCenter Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check left alignment
ElseIf strAlignment = "Left" Then
If Range(Cells(intRow, 1), Cells(intRow, 1)).HorizontalAlignment _
= xlLeft Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check right alignment
ElseIf strAlignment = "Right" Then
If Range(Cells(intRow, 1), Cells(intRow, 1)).HorizontalAlignment _
= xlRight Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check fill alignment
ElseIf strAlignment = "Fill" Then
If Range(Cells(intRow, 1), Cells(intRow, 1)).HorizontalAlignment _
= xlFill Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check justify alignment
ElseIf strAlignment = "Justify" Then
If Range(Cells(intRow, 1), Cells(intRow, 1)).HorizontalAlignment _
= xlJustify Then
CheckAlignment = True
Else
CheckAlignment = False
End If
Exit Function
'Check center across selection alignment
ElseIf strAlignment = "Center Across Selection" Then
If Range(Cells(intRow, 1), Cells(intRow, 1)).HorizontalAlignment _
= xlCenterAcrossSelection Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check distributed alignment
ElseIf strAlignment = "Distributed" Then
If Range(Cells(intRow, 1), Cells(intRow, 1)).HorizontalAlignment _
= xlDistributed Then
CheckAlignment = True
Else
CheckAlignment = False
End If
'Check general alignment
ElseIf strAlignment = "General" Then
If Range(Cells(intRow, 1), Cells(intRow, 1)).HorizontalAlignment _
= xlGeneral Then
CheckAlignment = True
Else
CheckAlignment = False
End If
End If
End Function
You can download the code and file related to this article here.
See also:
- Excel VBA Set Horizontal Alignment (Sample Code)
- Excel VBA, Horizontal 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