PDA

View Full Version : One message box in a for loop



coltaalex
07-06-2010, 09:12 AM
I have a for loop :

Sub color2()
Dim N As Long
For N = 1 To 56
If Cells(N, 16).Interior.ColorIndex = 3 Then
MsgBox "Codes don't Exist "
End If
Next N


End Sub


this code gives me message box for every cell which has color 3 (red)
i want just one message box, for all the cells in red

Tinbendr
07-06-2010, 09:33 AM
Sub color2_1()
Dim N As Long
Dim Temp$
For N = 1 To 5
If Cells(N, 16).Interior.ColorIndex = 3 Then
Temp$ = Temp$ & N & vbCr
End If
Next N

MsgBox "Rows" & vbCr & Temp$ & "Don't have codes."

End Sub


You really should have added this to the original question. (http://www.vbaexpress.com/forum/showthread.php?t=32861)

coltaalex
07-06-2010, 09:53 AM
Thank you very much, but this code is giving me the message box when i don't have red cells, i need message box just when i have red cells

Kenneth Hobs
07-06-2010, 12:04 PM
Sub color2()
Dim N As Long, s As String
s = ""
For N = 1 To 56
If Cells(N, 16).Interior.ColorIndex = 3 Then
s = s & Cells(N, 16).Address & vbLf
End If
Next N
If s = "" Then
MsgBox "P1 to P56 has no interior color red."
Else: MsgBox "Cells with interior color red:" & vbLf & s
End If
End Sub