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:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)' This subroutine colors a cell red when double-clicked then clears it when double-clicked again.
' Some values for .ColorIndex are...
' Red = 3, Green = 4, Blue = 5, Yellow = 6, Orange = 45
' Google "VBA color palette" for more colors
 
    ' If the cell is clear
    If Target.Interior.ColorIndex = xlNone Then
 
        ' Then change the background color to red
        Target.Interior.ColorIndex = 17
 
    ' Else if the cell background color is red
    ElseIf Target.Interior.ColorIndex = 17 Then
 
        ' Then clear the background
        Target.Interior.ColorIndex = xlNone
 
    End If
 
    ' This is to prevent the cell from being edited when double-clicked
    Cancel = True
 
End Sub

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

 

[JoeMo]

You'd have a better chance of getting some help if you post the function you want to modify.

 

[nanrem]

Hi JoeMo:

Thanks for the fast reply, you are totally right, didn't post the function, sorry about that:

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

This is the fuction, i pasted the wrong code.

 

[JoeMo]

Untested.

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

 

[nanrem]

Hi Joemoe:

Thanks for the reply friend. I tried the code but the count stays on 0. A friend helped with the code and the result was this:

[COLOR=blue]Function[/COLOR][COLOR=#333333] ColorFunction(rColor [/COLOR][COLOR=blue]As[/COLOR][COLOR=#333333] Range, rRange [/COLOR][COLOR=blue]As[/COLOR][COLOR=#333333] Range, Optional SUM [/COLOR][COLOR=blue]As[/COLOR][COLOR=#333333] [/COLOR][COLOR=blue]Boolean[/COLOR][COLOR=#333333]) [/COLOR]
    [COLOR=blue]Dim[/COLOR] i [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR], ii [COLOR=blue]As[/COLOR] [COLOR=blue]Long[/COLOR]     [COLOR=blue]For[/COLOR] i = 1 [COLOR=blue]To[/COLOR] rRange.Rows.Count         [COLOR=blue]For[/COLOR] ii = 1 [COLOR=blue]To[/COLOR] rRange.Columns.Count             [COLOR=blue]If[/COLOR] rRange(i, ii).Interior.ColorIndex = rColor.Interior.ColorIndex [COLOR=blue]Then[/COLOR]                 ColorFunction = ColorFunction + IIf(SUM, Val(rRange(i, ii).Value), 1)                 Exit [COLOR=blue]For[/COLOR]             [COLOR=blue]End[/COLOR] [COLOR=blue]If[/COLOR]         [COLOR=blue]Next[/COLOR]     [COLOR=blue]Next[/COLOR]  </pre>[COLOR=blue]End Function[/COLOR][COLOR=#333333] [/COLOR]

Tested and it works great. I shared it and i hope it help others out there. I appreciate the help. Have a nice Day :D