title: VBA's Worksheet_Change function using the Intersect method with Cells as the Range definer
body: <p>I am trying to make my spreadsheet autofill the corresponding cell when one of the related cells have been changed.
  I have previous just defined the target as:</p>
  
  <pre><code> If Target.Address = "$A$5" then
  </code></pre>
  
  <p>and had no issues.
  However, now my target can be one of many cells and I read that the intersect method should be able to work for this but when I input my code as:</p>
  
  <pre><code>If Intersect(Target, Range(Cells(12,2), Cells(12,j-1))) Is Nothing Then
  </code></pre>
  
  <p>(I am trying to change the cells below the target, with the target being any of the cells between 12B and 12(j-1) with j being previously defined)
  I get the following error:
  "Run-time error '1004': Application-defined or object-defined error"
  But from I can tell, my code is exactly the same as all the examples around.</p>
  
  <p>My full code is (although there may be an unrelated error with my vlookup as well)</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range(Cells(12, 2), Cells(12, j-1))) Is Nothing Then
      If IsEmpty(Target) Then
          Target.Interior.ColorIndex = 19
      Else:
          If Range("$A$13").Value = "" Then
              Range("$A$13").Value = "Care Type"
              Range("$A$13").Font.Bold = True
          End If
          Target.Interior.ColorIndex = xlNone
          Target.Offset(1, 0).Interior.ColorIndex = 19
          Target.Offset(2, 0).Value = Application.WorksheetFunction. _
              VLookup(Target, Sheets("Sheet2").Range("$E$3:$F$6"), 2)
          Target.Offset(2, 0).Font.Bold = True
  
          i = 2
          Do Until IsEmpty(Cells(11, i))
              If Cells(11, i).Value &lt;= ChildCount Then
                  Cells(12, i).Interior.ColorIndex = 19
              End If
          i = i + 1
          Loop
      End If
  End If
  End Sub
  </code></pre>