Type Mismatch Error only on second iteration of code

[breadperson]

Hi. I am managing a datasheet in which subjects have taken a survey (only those under age 17). I wrote a script to fill the remaining blank cells (w/out data) with the value 999 if the participant either is not included in study (column 3 cell value = 0) or if their age is >= 18. The script runs anytime the sheet experiences a change.

The script works great the first time I implement it and fills the blank cells correctly with 999's. However, if I then make a minor change (ex. change col 3 value from 1 to 0 to test) I get a type mismatch error on any subsequent iteration of the now updated sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False

Dim i As Integer
For i = 5 To 300
      If IsBlank(Worksheets("Sheet1").Cells(i, "A")) Then
           Worksheets("Sheet1").Range("D" & i & ":JF" & i).Value = ""         ' # If no SubID then keep blank#
      ElseIf Worksheets("Sheet1").Cells(i, 4).Value <> 999 And Worksheets("Sheet1").Cells(i, 3).Value = 0 Then
           Worksheets("Sheet1").Range("D" & i & ":JF" & i).Value = 999      ' #If  not already filled with 999 & Include = 0 then fill w/ 999#
      ElseIf Worksheets("Sheet1").Cells(i, 4).Value <> 999 And Worksheets("Sheet1").Cells(i, "D").Value = "" And Worksheets("Subject Info").Cells(i + 10, "F").Value >= 18 Then
           Worksheets("Sheet1").Range("D" & i & ":JF" & i).Value = 999     ' #If  not already filled with 999 & Age >= 18 then fill w/ 999 unless data exists in cells#
      End If
Next I
End Sub

Public Function IsBlank(ByRef rngToCheck As Range) As Boolean
    IsBlank = (CStr(rngToCheck.Cells(1).Value2) = vbNullString)
End Function

Relevant Info:
Excel Version 14.0.7188.5002 (32-Bit) - 2010
"Sheet1" column A and C (3 in code) are either numbers (1 or 0 in column 3, 1 - ~150 in column 1, else are "" as result of formula)
"Sheet1" column 4 is the start of data - all numbers


I've googled the problem extensively and searched the forum. I don't understand why the script runs the first time I try it but fails on subsequent attempts with the now updated sheet.

Thanks in advance!

 

[StephenCrump]

Welcome to the Forum!

On which line of code are you getting the Type Mismatch error?

You have set:

Application.ScreenUpdating = False

so this Worksheet_Change Sub won't be running a second time, unless you have set .ScreenUpdating to True somewhere else?