Multiple ranges in one VBA For loop?

Question

I am a beginner at VBA and am trying to highlight only empty cells in columns N and AA only. Can there be multiple ranges in one For loop to make this work instead of the following code?

Private Sub CommandButton22_Click()
    'HIGHLIGHT
    Dim cell As Range

    For Each cell In Range("N")
        If cell.Value = vbNullString Then
            cell.Interior.ColorIndex = 6
        End If
    Next cell

    For Each cell In Range("AA")
        If cell.Value = vbNullString Then
            cell.Interior.ColorIndex = 6
        End If

    Next cell
End Sub

Solution 1

edited to join the SpecialCells approach with the benefit of the UsedRange as pointed out by Thomas Inzina solution

Use SpecialCells() method of Range object and avoid looping

Private Sub CommandButton22_Click()
    'HIGHLIGHT
    Intersect(Union(Range("N:N"), Range("AA:AA")), ActiveSheet.UsedRange).SpecialCells(xlCellTypeBlanks).Interior.ColorIndex = 6
End Sub

Solution 2

Yes. Use the Application.Union method. That returns Areas / A collection of the areas, or contiguous blocks of cells, within a selection.

The following code works.

Private Sub CommandButton22_Click()
'HIGHLIGHT

Dim cell As Range
Dim target As Range

    Set target = Application.Union(ActiveSheet.Range("N:N"), ActiveSheet.Range("AA:AA"))

    For Each area In target.Areas
        For Each cell In area
            If cell.Value = vbNullString Then
                cell.Interior.ColorIndex = 6
            End If
        Next cell
    Next area
End Sub

It will color the entire column though. If you just want to color a subset, ex: from rows 10 through 22 in both columns, then change the union line to something like this

Set target = Application.Union(ActiveSheet.Range("N10:N22"), ActiveSheet.Range("AA10:AA22"))