IF with Count by Colour

[DYB]

I am trying to use a formula that counts a cell colour in one column based on a text value (YES or NO) in a different column. The formula I am using is

=IF(Enrolled_Accommodation="YES",CountByColor(Enrolments!EK$4:EK$1499,83,140,213),0)

The CountByColor is VBA code that works absolutely fine on its own but when combined with the IF statement it reports just the CountByColor value disregarding whether the 'Enrolled_Accommodation' value is YES or not.

The VBA code I am using is:

Public Function CountByColor(rng As Range, Red As Long, Green As Long, Blue As Long) As Long

Dim lCount As Long
Dim rngCell As Range
For Each rngCell In rng
If rngCell.Interior.Color = RGB(Red, Green, Blue) Then
lCount = lCount + 1
End If
Next
CountByColor = lCount
End Function

Is anyone able to tell me what I need to change in either the VBA or formula?

 

[jasonb75]

What does Enrolled_Accommodation refer to? Is it a single cell, or a range that is parallel to the one used in CountByColor which should be evaluated row by row?

In the case of the latter (which seems more likely) you would need to use IF inside of CountByColor, not outside as you have done. However, the way that the code is written will not allow you do this so the code will need to be changed if that is what is required.

 

[DYB]

Hi

It is a named range that is parallel to the column 'Enrolments!EK$4:EK$1499' which refers to a specific week of the year. The formula is used across the whole worksheet, column by column (i.e., EK, EL, EM, EN etc).

 

[jasonb75]

I haven't done any testing on this, it's just a quick edit to the code from your question. Hopefully no errors in there.

=CountByColor(Enrolled_Accommodation,"YES",Enrolments!EK$4:EK$1499,83,140,213)

Public Function CountByColor(rng As Range, crit As String, rng2 As Range, Red As Long, Green As Long, Blue As Long) As Long

Dim lCount As Long, rw As Long

For rw = 1 To rng.Rows.Count
    If rng2(rw).Interior.Color = RGB(Red, Green, Blue) And UCase(rng(rw).Value) = UCase(crit) Then
        lCount = lCount + 1
    End If
Next
CountByColor = lCount
End Function

 

[DYB]

Thank you so much as that seems to work fine!