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 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 " & ColumnNumberToLetter(col) & "; 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.
