1

I'm trying to create a MsgBox that automatically pops up with a prompt of "Yes or No" when a cell in a column changes from blank to "News", and to put the answer into the next column.

I will be continuing to add to rows over time so it has to automatically pop up when the cell changes from blank to "news" and input the answer into the newly added cell to the right.

I'm pretty sure I need the For each loop, but honestly I'm a little lost and get a mismatch error during debug at the If Intersect line. 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRange As Range

Set myRange = Range("G2:G1000")

If Intersect(myRange, Target) Then


If Range("G2").Value = "News" Then Answer = MsgBox("Good?", vbYesNo)
    Answer = ActiveCell.Offset(0, 1) = 1 'not sure if this is right, or is it Range.Offset?

Dim cel As Range
For Each cel In Range("G2:G1000")

    If cel.Value = "News" Then Answer = MsgBox("Good?", vbYesNo)
    Answer = ActiveCell.Offset(0, 1) = 1 'not sure if this is right, or is it Range.Offset?
    Exit For

Next

End If

End Sub
0m3r
  • 12,286
  • 15
  • 35
  • 71
oliver1337
  • 3
  • 3

1 Answers1

0

Here you go:

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Target.Column = 7 Then
        If Target.Count = 1 Then
            If LCase$(Target) = "news" Then
                Application.EnableEvents = False
                Target(, 2) = Array("Yes", "No")(MsgBox("Good?", vbYesNo) - 6)
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub
Excel Hero
  • 14,253
  • 4
  • 33
  • 40