Cannot get Range to pass into Function

You can pass a range to a UDF as a named range, or directly:

=BS(A1:G26, 3)

Hello dewittig,

Your macro works correctly as UDF when rewritten as shown here. It also will work correctly in VBA. When specifying a range in VBA you will need use the word Range, e.g. Range("C1:G25"). If you are referencing a range that is on a worksheet other than the ActiveSheet, you will need to prefix the range with the worksheet name.

Function BS(rng As Range, iRow As Integer)
  Dim c As Range
  Dim countt As Long
    'Test operations
    With rng.Rows(iRow)
      For Each c In .Cells
        Addx = c.Address
        If c.Interior.ColorIndex = 37 Then countt = countt + 1
      Next
    End With
    BS = countt
End Function

Hello dewittig,

The macro I wrote works fine in both instances. Your syntax is correct for the button and the first syntax for cell is also correct. Did you place the macro in a Standard VBA module? If not, the macro will not be visible to the worksheet.