You can try this for the MsgBox portion.

intResp = MsgBox("Do you qualify for Bank Day?", vbYesNo)
If intResp = 6 Then  '  Yes was clicked
Sheets(4).Cells(4, 3).Value = 1
End If
If intResp = 7 Then  '  No was clicked
Sheets(4).Cells(4, 3).Value = 0
End If

Steve Yandl

Private Sub Workbook_Open()
Dim ans
   If Month(Date) = 7 Then
       If Day(Date) >= 1 And Day(Date) < 5 Then
           If MsgBox("Do you qualify for Bank Day?", _
               vbYesNo) = vbYes Then
               Worksheets("Sheet4").Range("C4").Value = 1
           Else
               Worksheets("Sheet4").Range("C4").Value = 0
           End If
       End If
   End If
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code