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