ColorCellsBy Colour plus 2 more matcing criteria HELP, SO STUCK!!! :)

LaurenHancy

LaurenHancy

Board Regular
Joined
Aug 5, 2010
Messages
179
Hi All,

Hope you are having a great Tuesday. I desperately need your help.

I need to count the number of red cells (data range in colour C2:JC2) per person depending on the the following criteria:

Name = Collum A
By Month, Jan, Feb etc = Row C1:YC1 (MM/DD/YYYY)
"Lates" = Collum B

B34 is the *red* cell referance

I have the following formula already but I cant seem to add the other criteria into this formula. It works but I have to select which cells to count for each name and month. There are many names to contend with.

=CountCellsByColor('Daily Call KPI''s'!$C$2:$JC$2,$B34)


[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[TD="width: 64"]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl66"]01-Jan[/TD]
[TD="class: xl66"]02-Jan[/TD]
[TD="class: xl66"]03-Jan[/TD]
[TD="class: xl66"]04-Jan[/TD]
[TD="class: xl66"]05-Jan[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="class: xl67"]David[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="class: xl67"]Kelly[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]5
[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Red*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="class: xl67"]Jane[/TD]
[TD="class: xl65"]availibility[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]Lates
[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[TD="class: xl65"]*Green*[/TD]
[/TR]
</tbody>[/TABLE]

Thank you for you help.

L xx
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Hello,

Your UDF is a function which is supposed to produce what you are looking for ...

Haven' t you asked a very similar question not that long ago ... ???

https://www.mrexcel.com/forum/excel-questions/1091506-counting-coloured-cells-2-criteria.html

Code: 
[COLOR=#333333]Function CountCellsByFontColor(rData As Range, rMonth As Range)
[/COLOR]' for example = CountCellsByFontColor(C2:JC502, C1) '''''''''
' in cell C1 the Month such as : January
Dim indRefColor As Long
Dim cellCurrent As Range
Dim countRes
Dim lMonth As Long

Application.Volatile
countRes = 0
indRefColor = vbRed    ' Color Code for Red
lMonth = Month(DateValue("01 " & rMonth.Value & " 2019"))

    For Each cellCurrent In rData
        If cellCurrent.Interior.Color = indRefColor Then
           If Month(Cells(1, cellCurrent.Column)) = lMonth Then
                countRes = countRes + 1
           End If
        End If
    Next cellCurrent
        
CountCellsByFontColor = countRes 
End Function
 
Last edited:
Upvote 0
Hi James,

Thank you for your reply. Yes I did ask the question and I took code but I am stuck writing the formula, are you abel to tell me whats best to use? Thank you for your help, with both posts :)
 
Upvote 0
Can somebody please fix my formula

=IFERROR((SUMPRODUCT('Daily Call KPI''s'!C4:Y4, E6)*('Daily Call KPI''s'!A6:A50, A7)*('Daily Call KPI''s'!B6:B50 = "Availibility"),(CountCellsByColor('Daily Call KPI''s'!$C$7:$Y$7,$B20))
 
Upvote 0
Hi Laura,

There are only two possible colors : either the Font Color Or the Interior Color ...

1. Which one are you using ?

2. If it is the Interior Color ... is it Manually OR the result of some Conditional Formatting ???

3. If it is Conditional Formatting ... What is the rule which generates the Interior Color ???

4. Could you post your current version of CountCellsByColor ...???
 
Upvote 0
Code: 
Function GetCellColor(xlRange As Range)
    Dim indRow, indColumn As Long
    Dim arResults()
 
    Application.Volatile
 
    If xlRange Is Nothing Then
        Set xlRange = Application.ThisCell
    End If
 
    If xlRange.Count > 1 Then
      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
       For indRow = 1 To xlRange.Rows.Count
         For indColumn = 1 To xlRange.Columns.Count
           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Interior.Color
         Next
       Next
     GetCellColor = arResults
    Else
     GetCellColor = xlRange.Interior.Color
    End If
End Function
 
Function GetCellFontColor(xlRange As Range)
    Dim indRow, indColumn As Long
    Dim arResults()
 
    Application.Volatile
 
    If xlRange Is Nothing Then
        Set xlRange = Application.ThisCell
    End If
 
    If xlRange.Count > 1 Then
      ReDim arResults(1 To xlRange.Rows.Count, 1 To xlRange.Columns.Count)
       For indRow = 1 To xlRange.Rows.Count
         For indColumn = 1 To xlRange.Columns.Count
           arResults(indRow, indColumn) = xlRange(indRow, indColumn).Font.Color
         Next
       Next
     GetCellFontColor = arResults
    Else
     GetCellFontColor = xlRange.Font.Color
    End If
 
End Function
 
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByColor = cntRes
End Function
 
Function SumCellsByColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByColor = sumRes
End Function
 
Function CountCellsByFontColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByFontColor = cntRes
End Function
 
Function SumCellsByFontColor(rData As Range, cellRefColor As Range)
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim sumRes
 
    Application.Volatile
    sumRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Font.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Font.Color Then
            sumRes = WorksheetFunction.Sum(cellCurrent, sumRes)
        End If
    Next cellCurrent
 
    SumCellsByFontColor = sumRes
End Function

This is the code I have. I am using the interia colour and it is coloured red or green manually.

Thank you for your help.

L
 
Upvote 0
Thanks for the long list of UDFs ( User Defined Functions) :smile:

The one we need to concentrate on is the following

Code: 
Function CountCellsByColor(rData As Range, cellRefColor As Range) As Long
    Dim indRefColor As Long
    Dim cellCurrent As Range
    Dim cntRes As Long
 
    Application.Volatile
    cntRes = 0
    indRefColor = cellRefColor.Cells(1, 1).Interior.Color
    For Each cellCurrent In rData
        If indRefColor = cellCurrent.Interior.Color Then
            cntRes = cntRes + 1
        End If
    Next cellCurrent
 
    CountCellsByColor = cntRes
End Function

Now ... to correctly modify it ...

What are precisely the TWO conditions you need to add ...???
 
Last edited:
Upvote 0
Oh sorry about that :) I am quite new to UDF's, VBA Macros and all that shazz!

The criteria i require is:

Name (A)
Category (B)
Date (Row 1)

Thank you for your help with this James, its much appreciated :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

LaurenHancy
  • Question Question
Counting Coloured Cells with 2 criteria
2
Replies
15
Views
2K
James006
James006

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

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
Back
Top