Counting Coloured Cells with 2 criteria

LaurenHancy

Board Regular
Joined
Aug 5, 2010
Messages
179
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 change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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?
 
Upvote 0
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
 
Upvote 0
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? :eek:

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.
 
Upvote 0
Re: HELP!!!!! Counting Coloured Cells with 2 criteria :)

Is there any way I can combine CountCellsByColour and a IF statement?
 
Upvote 0
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:
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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.


Thank you for your help.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

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