Use a function in another workbook

[realniceguy5000]

Hi,

I wanted a way to count the number of cells that are red, after some research I found a function that worked. however the function is located in the workbook that I run my script from so the new workbook that is created via my script does not have the fuction in it.

How can I place the formula into the cell in the second workbook that uses the function in the main workbook?

Here is the color counting script that I am using. and the formula that I want in the cell looks something like this below. I suppose once I got the number of cells that are red I would then need to replace the formula with the value because once my main workbook closed the formula would not work anymore. Unless I could place the function in the new workbook?

Any Idea's?

Thank You, Mike

Range("B11").FormulaR1C1 = "=CountColor(R[10]C[10],R[11]C[10]:R[" & lrow & "]C[10])"

Function CountColor(rColor As Range, rSumRange As Range)


'Counts cells based on a specified fill color.=countcolor(B2,B1:B10)


Dim rCell As Range

Dim iCol As Integer

Dim vResult



iCol = rColor.Interior.ColorIndex



   For Each rCell In rSumRange

    If rCell.Interior.ColorIndex = iCol Then

         vResult = vResult + 1

    End If

   Next rCell



CountColor = vResult

End Function