conditional pop up message

[rubinda]

I have a file and am looking in column E and G. If Column E="Bob's" and column G="1234", I'd like a message to pop up stating "abc". How do I write this. I have the code below, but that only looks at individual columns.

Sub Bob's()
If Range("E3:E30").Value = "Bob's" Then MsgBox "If this Bob's order is for a 1234, 5678 or 9101, ensure the appropriate dish setting."
If Range("G3:G30").Value = "1234" Then MsgBox "If this 1234 order is for Bob's, ensure appropriate dish setting."
End Sub

 

[rpaulson]

try this,

Sub do_it()
For Each mycell In Range("E3:E30")
If mycell.Value = "Bob's" And Cells(mycell.Row, "G") = 1234 Then MsgBox "abc"
Next
End Sub

hth,
Ross

 

[mikerickson]

You could use Validation to do this.
Select E1 and set validation to the custom formula =AND($E1="Bob's",$G1="1234")
Set the Error type to Information and the Error message to abc

 

[rubinda]

Thank you. What if I want to implement and if/or statement. If G3:G30 is = 1234, 5678 or 9101. How would I write that?

 

[rpaulson]

try this

Sub do_it()
For Each mycell In Range("E3:E30")
r = mycell.Row
If mycell.Value = "Bob's" And (Cells(r, "G") = 1234 Or Cells(r, "G") = 5678 Or Cells(r, "G") = 9101) Then MsgBox '"abc"
Next
End Sub

 

[rubinda]

You are awesome! Last question and I am done with this file...

If I have a row, and my cell in column E="Bob's" and column G= 1234, 5678 or 9101, I'd like to change the text to red. Can you please assist?

 

[rpaulson]

Sub do_it()
For Each mycell In Range("E3:E30")
r = mycell.Row
Rows(r).Font.ColorIndex = xlAutomatic
If mycell.Value = "Bob's" And (Cells(r, "G") = 1234 Or Cells(r, "G") = 5678 Or Cells(r, "G") = 9101) Then
    MsgBox "abc"
    Rows(r).Font.Color = -16776961
End If
Next
End Sub