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:

1. strAlignment: The alignment selected from the drop down list to be matched with the alignments in column A.
2. 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