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 " &amp; col &amp; "; 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 &gt; 0 Then
        ColumnNumberToLetter = Chr(alpha + 64)
    End If

    If remainder &gt; 0 Then
        ColumnNumberToLetter = ColumnNumberToLetter &amp; 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 " &amp; ColumnNumberToLetter(col) &amp; "; 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 " &amp; ColumnNumberToLetter(col) &amp; "; 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 " &amp; ColumnNumberToLetter(col) &amp; "; please review."
        End If
    Next
End Sub


This is much clearer, less error-prone (it no longer makes assumptions about what the first column is), and more extensible. Almost perfect!

One last thing—instead of hard-coding which sheet you want this function to work on, consider passing in the sheet as an argument:

Sub ShowMsgForErrorColumns(sheet As Worksheet)
    Application.FindFormat.Interior.Color = vbRed

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

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


That makes this function reusable. You don't want to go too far with reusability (YAGNI), but at the same time, you don't want to hard-code assumptions or arbitrarily box yourself in. Sometimes, it's just more natural to parameterize a function.

The only thing that bothers me about this code is that setting the Application.FindFormat.Interior.Color property has global side-effects that are visible outside of the function. This bothers me for the same reason that global variables make my skin crawl. One way to solve this would be to save the original value at the top of the function, and then restore it at the end. But that might not be worth it, if you are disciplined about always making sure to set this property to the desired value before any calls to the Find function.