Need True/False formula if cell is colored

[jmitchells5w]

I'm looking for VBA code for a formula to determine if a cell is colored. The goal is to add cells that are colored (could be multiple colors) and figured if I could use a True/False formula to determine if a cell is colored then I could use an array formula to add the cells who have a corresponding "true" result. Thank you in advance for your help.

 

[jmitchells5w]

Another idea would be a formula which would return the color code of a cell. I could then add all the cells with a result greater than zero. This option would also allow me to add cells of varying colors if needed in the future. Thanks.

 

[mikerickson]

VBA can do this. But Excel is not built to read cell color as data. A helper column with data and Conditional Formatting is a more robust approach.

To answer your question, yes.....but it requires Names and the Get.Cell function.
Search on Get.Cell and a post explaining its use will be found.

One problem is that Get.Cell doesn't work with array arguments (or at least I haven't figured out how) which prevents its being used with SUMPRODUCT and the other functions that you will be wanting. (Excel isn't built to use cell color as data.)

 

[jmitchells5w]

I have found this bit of VBA code which creates a function called "CN" which will return the color code of a cell.

Function CN(CNbr As Range) As Integer
CN = CNbr.Interior.ColorIndex
End Function

One option is to add a column next to my data and copy the "CN" formula to get the color codes. I can then create an array formula adding my totals column if there is a color code greater than 0. I would like to build an array formula applying the "CN" function without creating the spare column. Any ideas? I've tried doing something like this:
'=SUMPRODUCT(--(CN(D8:D65536)),--(F8:F65536))
but it isn't working.

 

[Domenic]

How about the following custom function...

Function SumColor(SumRange As Range)
    Application.Volatile
    SumColor = 0
    For Each cell In SumRange
        If cell.Interior.ColorIndex <> -4142 Then
            SumColor = SumColor + cell
        End If
    Next cell
End Function

Then, the following can be used...

=SumColor(A2:A100)

Note that whenever you change a cell color, you'll need to force a calculation by pressing F9. However, if you enter any number in any cell, the function will update.

 

[mikerickson]

In re:Post #4, Rather than using a helper column with a UDF to read the color of the cell next to it, you could use Conditional Formatting to read the data entered into the helper column (that now contains no formulas, no UDF) and color the cell.

In addition to desiging your spreadsheet to make things easy for Excel, you might also want to make things easier for your user to interpret the spreadsheet. The problem "Does red mean East and blue, or is East = Blue?" can be avoided by putting East or West in the cell (the primary data) and then deriving (via ConditionalFormatting or VBA Change event if you have more that 3 cardinal directions) the color for highlighting.

I recall the bad old days of early computing and strongly advise against building a code (like red means East, blue means West...) into spreadsheet design. Plain languange spreadsheets (useable by vision impared users who rely on the computers ability to speak) are much easier to create, maintain and change (a year from now) than spreadsheets that rely on a user being able to remember a code.