modify target command code to prevent type 13 mismatch

I did a previous post that was solved (click on the link before reading on):

http://www.excelforum.com/excel-programming/778999-using-the-target-command-to-start-user-form-macro.html"
However, when I was performing scenarios where this code would 'fail' as a way to test it, I ran into one of the easiest ways it fails and would like to know how the code can be modified to fix this.

When you change the value in more than one cell via dragging the cursor, I get an

PHP Code: 

error code 13 type mismatch 


,

which I think I understand why and that's because my code is expecting a single cell change and multiple cells are being changed. I just don't know how to modify the code so that when a user would do this (which is highly likely for the application I am using the code for), the code would still work without an error.

Any suggestions?

For those who were lazy and did not click the link, here is the code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim answer As String
   
   If Not Intersect(Target, Sheet8.Range("I5:U5")) Is Nothing Then
      If Target.Value > Sheet15.Range("threshold") Then   
   
        formAsk.Show
         answer = formAsk.TextboxWhom
         If Sheet15.Range("therightname").Find(what:=answer) Is Nothing Then ' modified
            MsgBox "Please contact RP ALARA for dose approval."
         End If
         
         On Error GoTo BackupFile
         Open ThisWorkbook.Path & "\responselog.txt" For Append Access Write Lock Write As #2
         On Error GoTo 0
         
         Dim ThisUser As String
         'thisuser = Application.username ' registration in MS Office
         ThisUser = Environ("USERNAME") ' Windows login
         
         Print #2, Now & " User: " & ThisUser & " Response: " & answer
         Close 2
         
      End If
   End If
   
   
   
   Exit Sub
   
BackupFile:
         Open ThisWorkbook.Path & "\responselog2.txt" For Append Access Write Lock Write As #2
         Print #2, Now & " User: " & ThisUser & " Response: " & answer
         Close 2
   
End Sub

Assuming you want the same actions for each changed cell:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rngCell as range
   Dim answer As String
   
   If Not Intersect(Target, Sheet8.Range("I5:U5")) Is Nothing Then
    for each rngCell in Intersect(Target, Sheet8.Range("I5:U5"))
      If rngcell.Value > Sheet15.Range("threshold") Then   
   
        formAsk.Show
         answer = formAsk.TextboxWhom
         If Sheet15.Range("therightname").Find(what:=answer) Is Nothing Then ' modified
            MsgBox "Please contact RP ALARA for dose approval."
         End If
         
         On Error GoTo BackupFile
         Open ThisWorkbook.Path & "\responselog.txt" For Append Access Write Lock Write As #2
         On Error GoTo 0
         
         Dim ThisUser As String
         'thisuser = Application.username ' registration in MS Office
         ThisUser = Environ("USERNAME") ' Windows login
         
         Print #2, Now & " User: " & ThisUser & " Response: " & answer
         Close 2
         
      End If
    next rngCell
   End If
   
   
   
   Exit Sub
   
BackupFile:
         Open ThisWorkbook.Path & "\responselog2.txt" For Append Access Write Lock Write As #2
         Print #2, Now & " User: " & ThisUser & " Response: " & answer
         Close 2
   
End Sub