Excel VB Code seems to not be looping

[bobbly_bob]

Alrighty, not sure why this isn't working but I've only been reading up
on this lately.

What I need the code to do is to go through the range I2:I500 and if it
comes across a cell value, bring up a menu with the value two columns
across from the cell where a 1 was found, ask the question about
marking the cell, then move on to the next cell when the buttons have
been pressed.

At the moment, nothing happens at all when I try to run it.

Cheers

Sub Message_box_test()

Dim MyRange As Range
Set MyRange = Worksheets("Sheet1").Range("I2:I500")

For Each cell In MyRange
If ActiveCell.Offset(1, 0).Value = 1 Then

Msg = ActiveCell.Offset(0, 2).Value & vbCrLf & vbCrLf & "Would you like
this client to be marked as won?" & vbCrLf & vbCrLf & vbCrLf &
"(Hitting cancel will leave quote unmarked)"

Response = MsgBox(Msg, vbYesNoCancel + vbQuestion, Title)


If Response = vbNo Then


MsgBox "You Clicked No." & vbCrLf & vbCrLf & "Client will be marked as
not won."



End If


If Response = vbCancel Then


MsgBox "You clicked Cancelled." & vbCrLf & vbCrLf & "Client will not
yet be marked."


End If


MsgBox "You clicked Yes." & vbCrLf & vbCrLf & "Client will be marked as
won."

End If

Next

End Sub

 

[Nick Hodge]

Bob

During a for each...next loop the selection and therefore the ActiveCell
doesn't change.

Add a variable at the top

Dim myCell as Range

(This replaces the cell reference you use as using cell can be confused with
the cell property)

So you will now have

For Each myCell in myRange

Then replace all ActiveCell references with myCell

If myCell.Offset(1, 0).Value = 1 Then


--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk