Error when no records meet criteria - repost

[Pam]

I posted this problem on 3/24/10 and received a reply that didn't work as
needed. I've pasted the original messages below. I would appreciate any
help in correcting code to prevent error from occurring.

Thanks in advance,
Pam
____________________

Subodh,

Thank you for replying. I did as you suggested and now I'm getting the same
error at the next section of code where B2=5 and there are records that
match this criteria.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _

"=AND(RIGHT(D2)<>""p"",B2=5)"
Selection.FormatConditions(3).Interior.ColorIndex = 7

Do you have any other suggestions that I may try?
Thanks again,
Pam

Hi,

I have the following code and receive error 1004 Application defined or
object defined error on this line

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""r"",B2=4)"
Selection.FormatConditions(3).Interior.ColorIndex = 7

because there are no records in this instance of the report for "4" in
ColB.
I've searched and applied code all to no avail. Can someone please tell me
how to write code for when this may occur in any of the situations below?

Range("b2:b800").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""u"",B2=1)"
Selection.FormatConditions(1).Interior.ColorIndex = 4

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""u"",B2=2)"
Selection.FormatConditions(2).Interior.ColorIndex = 39

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""r"",B2=3)"
Selection.FormatConditions(3).Interior.ColorIndex = 7

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""r"",B2=4)"
Selection.FormatConditions(3).Interior.ColorIndex = 7

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""p"",B2=5)"
Selection.FormatConditions(3).Interior.ColorIndex = 7

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""p"",B2=6)"
Selection.FormatConditions(3).Interior.ColorIndex = 7

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""u"",B2=7)"
Selection.FormatConditions(3).Interior.ColorIndex = 7

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""u"",B2=8)"
Selection.FormatConditions(3).Interior.ColorIndex = 7

End Sub

Thanks in advance,
Pam

Try this
''''''' start of your code
On error resume next
' now the code that generated the error
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""r"",B2=4)"
Selection.FormatConditions(3).Interior.ColorIndex = 7
On error goto 0
' your more code goes here
End sub

 

[Bob Phillips]

Try this

With Range("B2:B800")

.Cells(1, 1).Select

.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""u"",B2=1)"
.FormatConditions(1).Interior.ColorIndex = 4

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(RIGHT(D2)<>""u"",B2=2)"
.FormatConditions(2).Interior.ColorIndex = 39

.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(AND(RIGHT(D2)<>""r"",OR(B2=3,B2=4)),AND(RIGHT(D2)<>""p"",OR(B2=5,B2=6)),AND(RIGHT(D2)<>""u"",OR(B2=7,B2=8)))"
.FormatConditions(3).Interior.ColorIndex = 7
End With

 

[Pam]

Bob,

It worked!! Thank you so much. I've spent a considerable amount of time
trying various forms of code to get this to work. You've saved me a lot of
time in sorting and manual formatting for each section.

I appreciate your help.
Pam

 

[Bob Phillips]

I am glad about that. I didn't spot your original email, otherwise I would
have posted there as I can see that the response you got didn't solve it.