title: VBA Run-time error '1004': Method 'Range' of object '_Global' Failed
body: <p>I'm trying to take a cell value from one workbook, and find if it occurs in a column on another workbook, returning a "yes" or "no" answer. I thought the best way to do this would be the Intersect command. I'm getting the "method 'range' of object 'global' failed" error.</p>
  
  <p>Any advice would be welcome.</p>
  
  <pre><code>' Next part puts a "Yes" or "No" in PHO column
  ' based on one-at-a-time comparison of new keys in column Y
  ' against Column C of today's PHO report
  Dim RowCount As Long
  Dim CurrentPHO As Workbook
  Dim TodaysDate As String
  Dim PHOCompare As Range
  Dim CurrentRow As String
  Dim CellCounter As Range
  
  TodaysDate = Format(Date, "m-d-yyyy")
  
  Set CurrentPHO = Workbooks.Open("\\netapp02\ProcurementDocs$\PHO\PHO " &amp; TodaysDate &amp; ".xlsx") 'This Opens and defines today's PHO report
  Set PHOCompare = CurrentPHO.Worksheets("Detail").Range("C:C")
  
  
  For RowCount = 2 To LastRow
      Set CellCounter = Workbooks("IBUT 11-4-14.xlsx").Sheets("Sheet1").Range("Y" &amp; RowCount)
      If Not Intersect(CellCounter, Range(PHOCompare)) Is Nothing Then
          Range("K" &amp; RowCount).Value = "No"
              Else: Range("K" &amp; RowCount).Value = "Yes"
      End If
  Next RowCount
  </code></pre>