VBA code to perform action from MsgBox

[CJG19]

Hi All,

I am trying to get a yes/no pop up box, which works, but if you select yes, change the active cell to Yes and turn green, if no, it should stay no but colour the active cell red, can anyone help please?

Sub Final_Account_Cert()

Range("BO5").Select
'Begin the loop
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "NO" And ActiveCell.Offset(0, -2) > 0 And ActiveCell.Offset(0, -1) = 0 Then
'Display a message to the user
MsgBox "Have We Received Final Account Certificate?", vbYesNo, "Thank you"
'Turn cell green and change text to Yes
If AnswerYes = vbYes Then
ActiveCell.Value = "YES"
ActiveCell.Interior.Color = 65280
End If
'Format Invoice Code Cell to Red
ActiveCell.Interior.Color = 255
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

TIA

CJG19

 

[Fluff]

How about

Sub Final_Account_Cert()

Range("BO5").Select
'Begin the loop
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "NO" And ActiveCell.Offset(0, -2) > 0 And ActiveCell.Offset(0, -1) = 0 Then
   'Display a message to the user
   If MsgBox("Have We Received Final Account Certificate?", vbYesNo, "Thank you") = vbYes Then
   'Turn cell green and change text to Yes
      ActiveCell.Value = "YES"
      ActiveCell.Interior.Color = 65280
   Else
      'Format Invoice Code Cell to Red
      ActiveCell.Interior.Color = 255
   End If
End If
   ActiveCell.Offset(1, 0).Select
Loop
End Sub

 

[CJG19]

Thank you so much @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

Duplicate to: Trouble with a loop

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.