Msg Box If Row contains trigger word

[somesoldiers]

Hi Guys

I would like a Msg Box to pop up if Row G or Row K contain the word "New" if not I want my macro to continue, having trouble with the If then, else bit, any ideas please?

Thanks

Noel

 

[villy]

Sub CheckNew()
if Range("G1")="New" or Range("K1")="New" then MsgBox "Found New"
 
End sub

 

[somesoldiers]

Thanks villy, sorry my fault I meant to say if the "New" is anywhere in columns G or K. thanks

 

[somesoldiers]

Thanks

 

[taurean]

Try this code:

Public Sub FindNew()
Dim r As Range, rSearch As Range
Set r = Union(Columns("G:G"), Columns("K:K"))
Set rSearch = r.Find(What:="New", After:=Range("G1"), LookIn:=xlValues, Lookat:=xlWhole)
If Not rSearch Is Nothing Then
MsgBox "Found New in: " & rSearch.Address
Else
'Continue your macro here!
End If
End Sub

 

[somesoldiers]

Hi Guys

this code works perfect but I would like to make a slight adjustment, instead of the macro shutting down when the user selects ok, I would like the user to be given the option with another message box "Do you want to ignore?". If they select yes, macro will continue on and highlght next cell with "New" in or continue to end? if they click ignore macro will shut down.

Apprectiate any help

Thanks

Noel

 

[taurean]

In that case, following option should work. I have commented the code all over so that you can understand the logic behind:

Public Sub FindNew()
Dim r As Range, rSearch As Range
Dim vbResult As VbMsgBoxResult
Dim firstAddress
'This creates a union of the specific columns and makes for easy reference in the code!
Set r = Union(Columns("G:G"), Columns("K:K"))
'This looks for the first instance of text "New"
Set rSearch = r.Find(What:="New", After:=Range("G1"), LookIn:=xlValues, Lookat:=xlWhole)
'If there's no cell with text "New then it will go to else
If Not rSearch Is Nothing Then
firstAddress = rSearch.Address
ContinueHere: 'See below in Case 1
'This sets an option of ignoring and going to the next cell
vbResult = MsgBox("Found New in: " & rSearch.Address _
& vbCrLf & "Do you want to ignore?", vbOKCancel, "FOUND CELL")
    
    Select Case vbResult
    Case 1 'Case Ignore
    Set rSearch = r.FindNext(rSearch)
        If rSearch.Address = firstAddress Then Exit Sub
        GoTo ContinueHere 'We'll use the same message box to avoid recursive code
    Case 2 'Case end macro
    'Continue here to end macro
    End Select
Else
'Continue here to end macro
End If
End Sub

 

[somesoldiers]

that's brilliant Taurean, thanks a lot for taking the time to help, will study your logic and am sure it will come in handy elsewhere