One message box in a for loop 4

[coltaalex]

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 whic has color 3 (red)
i want just one message box, for all the cells in red

 

[soccercook44]

So you want to break out of the for loop when a red cell has been found.

or you could always use a goto statement that goes to after the for loop once one red cell has been found.

 

[soccercook44]

exit for, not break.

haha

 

[DaveInIowa]

Keep a count of "bad" cells in your loop, then check this counter for a value greater than zero after the loop to display your error message.

 

[coltaalex]

could
you show me the code please ?

 

[soccercook44]

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


End Sub

 

[PHV]

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

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[coltaalex]

in a separate new sheet it working perfect, but when i'm using it in my macro, it doesn't work ,
here are my macro code :

Sub CodeExist2()
'
' CodeExist2 Macro
'

'
Range("P11").Select

Range("P1110000").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=COUNTIF(Code,P11)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(P11))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

Dim N As Long
For N = 1 To 9989
If Cells(N, 16).Interior.ColorIndex = 3 Then
MsgBox "Codes don't Exist "
Exit For
Else

End If
Next N



Range("P11").Select


End Sub

 

[electricpete]

1 - What kind of error message are you getting?
2 - Range("P11").Select is ambiguous. VBA may not know what tab to look in to find P11. Maybe add With Sheets("MySheet")... end with or something similar

 

[coltaalex]

i don't have error messages, and i deleted range("P11"), you are right it's irrelevant, i was recorded during the macro,

Macro runs ok, but is showing the message from the box, in both cases when i have red cells and when i don't

, but for loop is working corect in a new sheet, when i have red cells it's showing the message from the box, when i don't have red cells it's not showing the message from the box

 

[xlbo]

Conditional Format colours are not the same as cell interior.colorindex colours - they are seperate properties. you cannot check the colour of a cell for the result of a conditional format property

What you really need to do is test for the existance of your CF condition in your loop:

either "=COUNTIF(Code,P11)" or =LEN(TRIM(P11))=0

depending on what gives you your red colour

e.g.

Dim N As Long, i as long
i = 0
For N = 1 To 9989
If rtrim(Cells(N, 16).text = "" Then
i = i + 1

End If
Next N
if i > 0 then
msgbox "Your message here"
end if


Your alternative is to simply put a formula on the sheet at the end of the CF such as

Range("Q1").formula = "=IF(SUMPRODUCT((LEN(TRIM(P19989))=0)*(1))>0,""Codes Don’t Exist"","""")"

Which will do the same job but simply put the formula in a cell rather than pop up the message box - for 10000 lines the time difference should not be too great but if your data is likely to grow then you should consider using loops as sparingly as possible...

I think this is what Skip was referring to in a different thread....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[coltaalex]

why rtrim but bot trim

how it will be for count if - rcoutif

 

[xlbo]

VBA is different to excel worksheet functions

worksheet function TRIM

VBA functions LTRIM (trim leading spaces) and RTRIM (trim training spaces) perform the same functionality

there is no countif function in code - you can use it by using the eval method but there is little point as you can just write the formula out to the worksheet as I demonstrated...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question