How to modify custom function. Help

[nanrem]

Hi all:

I have a custom function that count a range of cells by a specific color that works great, but i need to modify it, so it doesn't count more than one cell in a row. This is the code:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

And this is the formula: "=colorfunction(A362,AP357:AV360,FALSE)"
were the first cell 'A362' is used as color reference.

For example, using the formula as reference, we will use cell 'A362' that is RED, to count from 'AP357:AV360', that means we have 7 columns and 4 rows, and we have 'AP357, AT357, AQ358 and AV360' marked with RED. So the new Formula should count only 3, because there are 2 marked cells in a same row. I have been trying this for weeks without results. Any help out there will be appreciated. Thanks

[WizzardOfOz]

I suggest instead of for each rCell in rRange you should loop rows and columns
(untested code below, may be minor typos)

for iRow = 1 to ubound(lCol,1)
for jCol = 1 to ubound(lcol,1)
for k = jCol+1 to ubound(lcol,1) -1
if lCol(iRow,k) = lcol(iRow,jCol) then lCol(iRow,k) = -1 'flag as already counted
next k
next jCol
next iRow

Then can continue as normal or include code in upper loop to add

[nanrem]

I suggest instead of for each rCell in rRange you should loop rows and columns
(untested code below, may be minor typos)

for iRow = 1 to ubound(lCol,1)
for jCol = 1 to ubound(lcol,1)
for k = jCol+1 to ubound(lcol,1) -1
if lCol(iRow,k) = lcol(iRow,jCol) then lCol(iRow,k) = -1 'flag as already counted
next k
next jCol
next iRow

Then can continue as normal or include code in upper loop to add

Hi wizzardOfOz:
Thanks for the reply, i really didn't get it, but a friend on a forum helped with the function and ended with this code

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM AsBoolean) 
    Dim i As Long, ii As Long     For i = 1 To rRange.Rows.Count         For ii = 1 To rRange.Columns.Count             If rRange(i, ii).Interior.ColorIndex = rColor.Interior.ColorIndex Then                 ColorFunction = ColorFunction + IIf(SUM, Val(rRange(i, ii).Value), 1)                 Exit For             End If         Next     Next  End Function

I share it and hope it help someone else. I know there are a lot of people out there with similar situations. Thanks a lot WizardOfOz, i really appreciate your help bro. Have a nice day