How to modify custom Function?? Help.

nanrem

New Member
Joined
Sep 22, 2014
Messages
4
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.:confused: This is the code:

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 :mad: Any help out there will be appreciated. Thanks
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You'd have a better chance of getting some help if you post the function you want to modify.
 

nanrem

New Member
Joined
Sep 22, 2014
Messages
4
Hi JoeMo:

Thanks for the fast reply, you are totally right, didn't post the function, sorry about that:
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

This is the fuction, i pasted the wrong code.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Untested.
Code:
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

New Member
Joined
Sep 22, 2014
Messages
4
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:
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top