Further Help with Colorfunction and Countif

[amyg12]

Hello I was wondering if anyone could help I got the below from a post on here quite a while ago which is brilliant it has helped with our spreadsheet but we are having a couple ofissues which I wondered if anyone could possibly help me resolve.

1. We have quite a large amount of data we need touse this on and it seems every time we try to pull the formula down it crashesthe programme any advice.
2. Also we were wondering if there was possibly away to adapt this to pull the information from different tabs I have used thisformula"=SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$4:$A$14&"'!F:F"),B2))"to do a normal countif across the tabs for specific data.
Basically we get leads and there are 132different lead names so that's what the above formula works fine for but thesales rep color code the lead for appropriate, inappropriate, converted and nonconverted so we need to count how many of each lead name are each colour eitherin each tab but preferably across all the tabs.

I hope that makes sense and that you can help before I pull out all of my hair

"See if this modification works for you. I added another argument. Your formula would look like this:

=ColorCountifFunction($C$3,"BR",C4:C74,FALSE)

The code has not been tested very much and I don't think a non string value is coded correctly for the second argument so the SUM functionality probably won't work. It should do what you need though.


Code:
Function ColorCountifFunction(rColor As Range, tValue As Variant, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com

'Sums or counts cells based on a specified fill color.
' tValue is a string that would be used in a Countif function
'''''''''''''''''''''''''''''''''''''''

lCol = rColor.Interior.ColorIndex

If SUM = True Then
For Each rCell In rRange
If (rCell.Interior.ColorIndex = lCol) And (rCell.Value = tValue) Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If (rCell.Interior.ColorIndex = lCol) And (rCell.Value = tValue) Then
vResult = 1 + vResult
End If
Next rCell
End If

ColorCountifFunction = vResult
End Function