title: application-defined or object-defined error in cell range visual basic
body: <p>Having some problems with the code below.  I'm getting an Application-defined or object-defined error in the initiation of a second for loop.  The format of the second loop range is what seems to be causing the problem.  Removing the Sheets() object gets rid of the error, but then the script reads from the wrong worksheet, and doesn't return any data.  </p>
  
  <p>The goal of this code is to loop over a vertical array of data, and then if a match to a selection from a dropdown is found, it loops over a horizontal array of data and returns a color change if it finds a 'Yes' value.</p>
  
  <pre><code>If Not Intersect(Target, Range("countryProductCell")) Is Nothing Then
      lastcolumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
      Dim cellRow As Integer
      cellRow = Target.Row
      Dim defaultCellColumn As Integer
      defaultCellColumn = 4
      i = 5
      j = 1
      k = 1
      If Not Cells(cellRow, defaultCellColumn).Value = "(Select Title)" Then
          For Each countryCell In Range(Cells(cellRow, defaultCellColumn + 1), Cells(cellRow, lastcolumn))
              If countryCell.Value = "Use Default" Then
                  countryCell.Interior.ColorIndex = 3
              End If
          Next
          For Each nameCell In Sheets("Active Product Catalog").Range("ProductNames")
              If nameCell.Value = Cells(cellRow, defaultCellColumn).Value Then
                  'Error on the line below!
                  For Each purchaseableCell In Sheets("Active Product Catalog").Range(Cells(nameCell.Row, 10), Cells(nameCell.Row, 27))
                      If purchaseableCell.Value = "Yes" Then
                          'If Purchaseable, Change Color
                          Sheets("Home Template").Cells(cellRow, defaultCellColumn + j).Interior.ColorIndex = 35
                      End If
                      j = j + 1
                  Next
              End If
              k = k + 1
          Next
      ElseIf Cells(cellRow, defaultCellColumn).Value = "(Select Title)" Then
          If Target.Value = "(Select Title)" Then
              Target.Interior.Color = Cells(Target.Row, Target.Column - 1).Interior.Color
              For Each countryCell In Range(Cells(cellRow, defaultCellColumn + 1), Cells(cellRow, lastcolumn))
                  If countryCell.Value = "Use Default" Then
                      countryCell.Interior.ColorIndex = 2
                  End If
                  i = i + 1
              Next
          ElseIf Target.Value = "Use Default" Then
              Target.Interior.ColorIndex = 2
          ElseIf Application.VLookup(ActiveSheet.Cells(cellRow, Target.Column), Sheets("Active Product Catalog").Range("E:AK"), Target.Column, False) = "Yes" Then
              Target.Interior.ColorIndex = 35
          ElseIf Not Application.VLookup(ActiveSheet.Cells(cellRow, Target.Column), Sheets("Active Product Catalog").Range("E:AK"), Target.Column, False) = "Yes" Then
              Target.Interior.ColorIndex = 3
          End If
      End If
  End If
  </code></pre>