assign a range to white cells (non color) excel

Question

I have an attendance form. Cells A1-G1, which will get an X for present. I have it set up to calculate the attendance percentage. I also have it set up to change the calculation based on number of days eligible ie new person starts mid month, they would only have 15 days, changing the numerator. I have to manually change the range for the countif statement.

A1:G1 is the range J1 is the area we'd put in for how many days eligible (this is for those who are not on the whole term) I3 is the default days eligible (regular whole term)

=COUNTIF(A1:G1,"X")/(IF(J1="",I3,J1))

This works fine, I'd like to set it up to change the countif to only count the range that has white cells vs other colors. Change the A1:G1 to say C1:G1 if cells A1, B2 were red.

I can explain for if needed. I see lots on how to change the color of a cell, but not to act on a color in countif.

D

No, any color really. I use a level of gray. I 'gray' out when they were not enrolled. So, only the cells without color should be included. No conditional formatting. — Dennis
You'll need to use VBA for this. Though I would recommend a different approach if possible than relying on cell colors. — BigBen
I'm all for suggestions. I'm ok with using VBA, briefly looked into that with dynamic arrays/ranges. What other way would denote the change? — Dennis

BigBen BigBen · Accepted Answer · 2019-11-18T02:04:10

Excel doesn't have in-built functionality for handling colors within formulas. You need to use VBA. But a note of caution, the following UDF won't recalculate unless you manually refresh. The proposed solution also uses SUMPRODUCT instead of COUNTIF:

Public Function HasNoFill(ByVal rng As Range) As Boolean()

    Dim temp() As Boolean
    ReDim temp(1 To rng.Count)

    Dim cell As Range
    For Each cell In rng
        Dim i As Long
        i = i + 1

        temp(i) = cell.Interior.Color = 16777215
    Next

    HasNoFill = temp
End Function

assign a range to white cells (non color) excel

1 Answers