title: How to fix VBA error message Run-time Error '1004' Application-defined or Object-defined error
body: <p>I have the following code to make a column display desired statuses depending on the entries of 2 other columns. </p>
  
  <p>Below are the codes.</p>
  
  <p>Also, can anyone please explain to me what is the point of repeating those conditions in 2 codes <code>Sub compare2 ()</code> and <code>Public Sub single_change(changed_cell As Range)</code>? This code is developed by an intern at my company. It makes sense to me for the use of the first but not the second code. </p>
  
  <p>The <code>Public Sub single_change(changed_cell As Range)</code> kept being highlighted by Excel for error message: </p>
  
  <blockquote>
    <p>Compile Error: Loop without Do, Else without If. </p>
  </blockquote>
  
  <p>Then after that error, I got another on <code>Set sales_cell = changed_cell.Offset(, -1)</code> with error: </p>
  
  <blockquote>
    <p>Application-defined or object-defined error</p>
  </blockquote>
  
  <p>Thanks!!!</p>
  
  <pre><code>Sub compare2()
  
  Dim i As Long
  Dim A As Long
  Dim B As Long
  Dim c As Long
  
  A = 14
  B = 15
  c = 16
  
  Do While A &lt;= 42
      i = 2
      Do Until Len(Cells(i, A)) = 0
  
          If Cells(i, A) = "Rollup" And Cells(i, B) = "Green" Then
          Cells(i, c) = "Green"
  
          ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Yellow" Then
          Cells(i, c) = "Yellow"
  
          ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Red" Then
          Cells(i, c) = "Red"
  
          ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Overdue" Then
          Cells(i, c) = "Overdue"
  
          ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Rollup" Then
          Cells(i, c) = "Rollup"
  
  Else        
  
  End If
  
  i = i + 1
  
  Loop
  
  A = A + 4
  B = A + 1
  c = A + 2
  
  Loop
  
  End Sub
  </code></pre>
  
  <pre><code>Public Sub single_change(changed_cell As Range)
  
      Dim sales_cell As Range
      Dim production_cell As Range
      Dim day_cell As Range
  
      If changed_cell.Column Mod 2 = 0 Then
          Set sales_cell = changed_cell
          Set production_cell = changed_cell.Offset(, 1)
          Set day_cell = production_cell.Offset(, 1)
      Else
          Set production_cell = changed_cell
          Set sales_cell = changed_cell.Offset(, -1)
          Set day_cell = production_cell.Offset(, 1)
      End If
  
      On Error GoTo multiple_changes
  
          If sales_cell = "Rollup" And production_cell = "Green" Then
          day_cell = "Green"
  
          ElseIf sales_cell = "Rollup" And production_cell = "Yellow" Then
          day_cell = "Yellow"
  
          ElseIf sales_cell = "Rollup" And production_cell = "Red" Then
          day_cell = "Red"
  
          ElseIf sales_cell = "Rollup" And production_cell = "Overdue" Then
          day_cell = "Overdue"
  
          ElseIf sales_cell = "Rollup" And production_cell = "Rollup" Then
          day_cell = "Rollup"
  Else
  
  End If
  
  Exit Sub
  
  
  multiple_changes:
  
  Dim i As Long
  Dim A As Long
  Dim B As Long
  Dim c As Long
  
  A = 14
  B = 15
  c = 16
  
  
  Do While A &lt;= 42
      i = 2
      Do Until Len(Cells(i, A)) = 0
  
          If Cells(i, A) = "Rollup" And Cells(i, B) = "Green" Then
          Cells(i, c) = "Green"
  
          ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Yellow" Then
          Cells(i, c) = "Yellow"
  
          ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Red" Then
          Cells(i, c) = "Red"
  
          ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Overdue" Then
          Cells(i, c) = "Overdue"
  
          ElseIf Cells(i, A) = "Rollup" And Cells(i, B) = "Rollup" Then
          Cells(i, c) = "Rollup"
  Else
  
  End If
  
  i = i + 1
  
  Loop
  
      A = A + 4
      B = A + 1
      c = A + 2
  
  Loop
  
  End Sub
  </code></pre>
  
  <p>Thank you very very much!</p>