# Counting Coloured Cells with 2 criteria

#### LaurenHancy

##### Board Regular
Hello All!

I am quite stuck and I am sure you lovely lot will be able to solve my mystery!

So my aim is to find out which cells with a criteria of "Name" & "Date" are red. When a person is late, the cell which is a time, is colored Red.

I have names in cells A, and the date in the header going from C5:YC5. Each person has their own row and times in the cells, when they are late, I colour the cell red. is there any way to count the number of red cells per name for each month, Jan, Feb etc?

I hope you can help me

Thank you.

X

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### LaurenHancy

##### Board Regular
Re: HELP!!!!! Counting Coloured Cells with 2 criteria

Ok so I have managed to work out how to add a VBA code for =CountCellsByColor but for some reason it wont count the colours which have been added as part of a conditional format. Any suggestions on how to get around this?

#### DanteAmor

##### Well-known Member
Re: HELP!!!!! Counting Coloured Cells with 2 criteria

In 2010+ excel version you can use activecell.DisplayFormat.Interior.Color, but if you have 2007, then you have to modify the macro to analyze the same conditioner that has the conditional format and then count the cell or not.

What version of excel do you have?
You can put the code CountCellsByColor
And an example of where you have the cells

#### LaurenHancy

##### Board Regular
Re: HELP!!!!! Counting Coloured Cells with 2 criteria

Thank you for your response. I am working with 2013. I have added the code CountCellsByColor but I am unable to add a criteria like month etc.

I am not to sure how to add an example of the spreadsheet I am using?

A B C D
Names 01/01/19 02/01/19 03/01/19.....
Lauren 08:59 09:10 10:00
James 09:30 08:40 10:21
John 10:00 09:00 09:01

Instead of the text being red, it would be the cell. I want to know by month and person, how many red cells there are.

#### LaurenHancy

##### Board Regular

Re: HELP!!!!! Counting Coloured Cells with 2 criteria

Is there any way I can combine CountCellsByColour and a IF statement?

#### James006

##### Well-known Member
Re: HELP!!!!! Counting Coloured Cells with 2 criteria

Hello,

Why don't you post your current UDF ... CountCellsByColor ... since a tiny modification will do the job ... :wink:

#### LaurenHancy

##### Board Regular

Re: HELP!!!!! Counting Coloured Cells with 2 criteria

Thank you James for your help, I am going to sound really stupid now... I am not sure how to upload an example on here!? eeeek!

#### Fluff

##### MrExcel MVP, Moderator
Re: HELP!!!!! Counting Coloured Cells with 2 criteria

Simply copy the code of your CountCellsByColour function, then in the reply window click the # icon & paste the code between the tags that appear.

Also are the cells coloured manualy or by conditional format?

#### LaurenHancy

##### Board Regular
Re: HELP!!!!! Counting Coloured Cells with 2 criteria

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

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``````

The colours are completed mannually and by conditional formatting.

#### James006

##### Well-known Member
Re: HELP!!!!! Counting Coloured Cells with 2 criteria

Below is your Function modified ...

Code:
``````Function CountCellsByFontColor(rData As Range, rMonth As Range)
' for example = CountCellsByFontColor(B2:BH2, B6) '''''''''
' in cell B6 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``````

Hope this will help

Last edited:

Replies
1
Views
53
Replies
1
Views
24
Replies
2
Views
50
Replies
10
Views
49
Replies
2
Views
34