User Defined Function Fails when I run a Macro

I have borrowed a UDF from another site. It has the simple function of finding the text color in a cell. It works perfectly when I use it as part of a formula. Here is the UDF:

Function txtColor(rng As Range)
txtColor = rng.Font.ColorIndex
End Function

I have a macro that filters cells based on the a formula using this UDF. When I filter using this formula by hand, the filter process works fine. But when I use my macro -- which filters, then copies, then pastes, the formula no longer works. All cells containing that formula transform into #VALUE!.

Thank you in advance for any suggestions.

Scott

You can autofilter filter by color without using any functions.

Please take a few minutes to read the forum rules and then edit your post to add CODE tags. While you're there, you might want to read the part about "How to get your questions answered quickly"

Thanks.

Yes -- as the rules suggest, post a workbook that illustrates the problem.

Try this code instead:

Sub Macro2()
  Dim wksScr        As Worksheet
  Dim i             As Long

  Set wksScr = Worksheets("Script")

  Application.Calculation = xlCalculationManual
  
  With wksScr
    For i = 2 To Worksheets.Count
      Worksheets(i).Range("A2:F2000").ClearContents

      With .Range("A9", .Cells(.Rows.Count, "E"))
        .AutoFilter Field:=2, Criteria1:=Worksheets(i).Name & "."
        .AutoFilter Field:=5, Criteria1:="Yes"
      End With

      .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Worksheets(i).Range("A2")
    Next i
    .AutoFilterMode = False
  End With
  
  Application.Calculation = xlCalculationAutomatic
End Sub

Thank you Shg for your kindness. I am fairly new to VBA (as I am sure is obvious). So I may have incorporated your code into my macro incorrectly. It seems to run most of the way. But near the end, I get a message: "Run Time Error 91: Object variable with Block variable not set"

When I press debug, it takes me to this line

.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Copy Worksheets(i).Range("A2")

In case it helps, I have attached the worksheet showing how I tried to incorporate your code.

I apologize that my limits are making it hard for you to help me.

Scott

There's no "incorporating" to do. Replace all of your code with my code.

See attached.

You're welcome.