title: Excel VBA Update cell value in table based on condition
body: <p>I have a table which gets data via a URL. The table has the following structure</p>
  
  <pre><code>Issue Key   Assignee   Status         Tester   Developer
  1           John       In Progress             John
  2           Peter      In Testing     Peter    May
  </code></pre>
  
  <p>Tester and Developer columns are automatically filled when Status is changed.</p>
  
  <pre><code>If Status = In Progress, Developer = Assignee (in this case, John)
  If Status = In Testing, Tester = Peter (in this case, Peter)
  </code></pre>
  
  <p>We have No Else part in If statement</p>
  
  <p>I build a macro to get this thing as follows</p>
  
  <pre><code>Private Sub Worksheet_Change(ByVal Target As Range)
  
  Const STATUS_INPROGRESS = "In Progress"
  
  Dim tbl As ListObject
  Dim x As Long
  
  Set tbl = ActiveSheet.ListObjects("S70")
  
  If Not (Application.Intersect(Target, ActiveSheet.ListObjects("S70").DataBodyRange) _
    Is Nothing) Then
      For x = 1 To tbl.ListRows.Count
          With tbl.ListRows(x)
              If .Range(1, 3).Value2 = STATUS_INPROGRESS Then 'Developer colum
                  .Range(1, 5).Value2 = .Range(1, 2).Value2 
              End If
          End With
      Next
  End If
  End Sub 
  </code></pre>
  
  <p>The macro check if the status is In Progress, it will assign a value from Assignee to Developer. It throws an error every time I made a change on table range, the error is "<strong>method value2 of object range failed</strong>"
  It happens on the assigned part.</p>
  
  <pre><code>.Range(1, 5).Value2 = .Range(1, 2).Value2
  </code></pre>
  
  <p>If I changed to <code>.Value</code>, the same error happens. Could you help me with this?</p>