Find cells with specific formatting

I'm currently writing code so that when a red-colored cell appears in a column, a message box will appear letting me know that column has an error. I have written the following method:

Sub MSG()

    Dim a
    Dim B

    With Application.FindFormat.Interior
        .color = vbRed
    End With

    '/ Sheet1 is example sheet name
    Set a = Sheet1.Columns(1).Find(What:="", SearchFormat:=True)
    Set B = Sheet1.Columns(2).Find(What:="", SearchFormat:=True)

    If Not a Is Nothing Then
          MsgBox ("There is an issue with column A, please review.")
    End If
    If Not B Is Nothing Then
          MsgBox ("There is an issue with column B, please review.")
    End If

End Sub

But as you can see, expanding this to support an arbitrary number of columns would be impossible, and going up to column ED would require 134+ lines of code to be written!

Is there a more efficient way to do this? Would it be possible to optimize this code?

The basic construct you're looking for is a loop. This is what you should reach for any time you find yourself writing the same code (performing the same operation) over and over again, but in a regular pattern (e.g., on columns A–ED).

There are several different types of loops supported by VBA (consult your favorite language reference for details), but you probably want a ranged loop here, since you want to loop through columns A through ED (or whatever maximum column). In VBA parlance, that would be a For loop; something like this:

Sub ShowMsgForErrorColumns()
    Application.FindFormat.Interior.Color = vbRed

    For col = 1 To 134
        Dim match
        Set match = Sheet1.Columns(col).Find(What:="", SearchFormat:=True)

        If Not match Is Nothing Then
            MsgBox "There is an issue with column " & col & "; please review."
        End If
    Next
End Sub

Notice that I have made the following additional improvements to your original code:

-
I have replaced MSG with a meaningful and descriptive name for the subroutine. This might not be the perfect name, but it is certainly a major improvement. It's also less likely to conflict with other identifiers (msg is pretty common, and VBA isn't case-sensitive).

-
The With…EndWith construct is only useful when you are setting multiple properties at a particular level of nesting. Your With block only had one statement inside of it, so I've removed it and replaced it with a single statement.

-
I believe variables should be declared as close as possible to the point of use. Ideally, they would be declared and initialized all at once, but if I remember correctly, VBA doesn't actually support this; you have to break it up into two separate lines. Still, keep 'em close! Also, keep them as narrowly scoped as possible—notice that here, I've put the variable declaration inside of the loop, instead of outside.

-
If a subroutine takes only one argument, you should not wrap its argument list in parentheses. Thus, I've removed the parentheses around the string passed in the call to the MsgBox subroutine.

But the code I've written above isn't quite what you want—we still need to fix a couple more things!

The first issue is that it will display a message box containing the numeric ID of the problematic column, rather than the alphabetic ID displayed in the Excel UI. For your user's sake, you probably want to change this. Since you're using a loop with the numeric column ID as the loop counter, you need an algorithm to systematically convert that numeric ID to the alphabetic ID. I would define a reusable function for this. The following function is adapted from one appearing in a Microsoft support article:

Function ColumnNumberToLetter(col As Integer) As String
    Dim alpha As Integer
    alpha = col \ 27

    Dim remainder As Integer
    remainder = col - (alpha * 26)

    If alpha > 0 Then
        ColumnNumberToLetter = Chr(alpha + 64)
    End If

    If remainder > 0 Then
        ColumnNumberToLetter = ColumnNumberToLetter & Chr(remainder + 64)
    End If
End Function

Then, just plug in a call to this function in the appropriate spot:

Sub ShowMsgForErrorColumns()
    Application.FindFormat.Interior.Color = vbRed

    For col = 1 To 134
        Dim match
        Set match = Sheet1.Columns(col).Find(What:="", SearchFormat:=True)

        If Not match Is Nothing Then
            MsgBox "There is an issue with column " & ColumnNumberToLetter(col) & "; please review."
        End If
    Next
End Sub

Also, now that we've got a loop, we no longer have to arbitrarily limit ourselves to going through column ED. It is simple to tweak this code to support any number of columns in a sheet—you just make the loop's upper bound the sheet's last column:

Sub ShowMsgForErrorColumns()
    Application.FindFormat.Interior.Color = vbRed

    Dim totalColumns As Integer
    totalColumns = Sheet1.Columns.Count

    For col = 1 To totalColumns
        Dim match
        Set match = Sheet1.Columns(col).Find(What:="", SearchFormat:=True)

        If Not match Is Nothing Then
            MsgBox "There is an issue with column " & ColumnNumberToLetter(col) & "; please review."
        End If
    Next
End Sub

In fact, though, we can do even better. VBA supports a special type of For loop that can be used to automatically iterate through an entire collection of values (in this case, all of the columns in a sheet). This is the For Each loop, and it can be used like so:

Sub ShowMsgForErrorColumns()
    Application.FindFormat.Interior.Color = vbRed

    For Each col In Sheet1.Columns
        Dim match
        Set match = Sheet1.Columns(col).Find(What:="", SearchFormat:=True)

        If Not match Is Nothing Then
            MsgBox "There is an issue with column " & ColumnNumberToLetter(col) & "; please review."
        End If
    Next
End Sub

This is much clearer, less error-prone (it no longer makes assumptions about w

StackExchange Code Review Q#153589, answer score: 7