title: UDF as criteria for advanced filter within macro error
body: <p>If I use a UDF to generate criteria for Advanced Filter, and run the advanced filter using VBA, a <code>1004</code> error is generated within the UDF.</p>
  
  <p>If the Advanced Filter is called from Excel, the Filter functions as expected.</p>
  
  <p>Why the difference?</p>
  
  <p>(And yes, I know there are other methods that can be used. I am trying to understand the difference between calling the Advanced Filter from Excel vs VBA when using this UDF as a criteria).</p>
  
  <p>I am filtering to return the entire row, if any item in the row has a red font (RGB 255,0,0).  The UDF is within the code below.</p>
  
  <p>In the screenshot below, the criteria formula are:</p>
  
  <pre><code>A2: =IsRed(A8)
  B3: =IsRed(B8)
  C4: =IsRed(C8)
  </code></pre>
  
  <p><em>The screenshot shows the Advanced Filter functioning as designed when called from Excel</em></p>
  
  <p><a href="https://i.stack.imgur.com/9PlY2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9PlY2.png" alt="enter image description here"></a></p>
  
  <p>But when the code below is run, after the column headers are copied to <code>E1:G1</code>, the code stops <strong>within the UDF</strong> with the above error message.  At the time <code>R.Address</code> = <code>A8</code></p>
  
  <p>I also tried recording code when I did the Advanced Filter from Excel, and then executing that recorded code instead of that below. This resulted in the same error.</p>
  
  <hr>
  
  <pre><code>Option Explicit
  Sub marine()
      Dim rTable As Range
      Dim rCriteria As Range
      Dim rDestination As Range
  
  Set rTable = Range("a7").CurrentRegion
  Set rCriteria = Range("a1:c4")
  Set rDestination = Range("E1")
  
  rDestination.Resize(columnsize:=3).EntireColumn.Clear
  
  With rCriteria
      .Rows(1).ClearContents
      .Cells(2, 1).Formula = "=IsRed(A8)"
      .Cells(3, 2).Formula = "=IsRed(B8)"
      .Cells(4, 3).Formula = "=IsRed(C8)"
  End With
  
      rTable.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
          rCriteria, CopyToRange:=rDestination, Unique:=False
  End Sub
  
  Function IsRed(R As Range) As Boolean
  
  'Runtime error '1004' on next line when above macro is run
      IsRed = (R.Font.Color = RGB(255, 0, 0))
  End Function
  </code></pre>
  
  <hr>