Count coloured cells without a macro?

Question

Is it possible to count cells by their colour (not by their cell value as they are blank) in Excel 2010/2013 without using VBA, as my files needs to be saved as .XLSX?

Solution

No, it is not possible to determine the color of a cell without using VBA.

It is possible to create a function in VBA and then use excel itself to continue from there, but VBA will always be required. Alternatively, don't rely on colors but on values somewhere in the sheet, instead.

Comments

[Excellll]

Welcome to Super User! Are the cells colored using conditional formatting? Or are they colored using regular cell formatting? If it's the former, this can probably be done without VBA.

[Mr Mystery Guest]

The cells are coloured manually as data is added to the spread sheet. The only thing I can think of to use conditional formatting to drive the colours is to use bold text on the cells values - but determining if text is bold requires VB (methinks)

[Steven]

What does the color (or bold) indicate in your spreadsheet? Can the color be determined by the value of the cell or row?

[Mr Mystery Guest]

The colour indicates a state ("good", "bad", "okay") . The value of the cell is an integer (a reference) which cannot change. I thought of adding dropdown boxes to determine state but that would get convoluted.

[Richard]

I'd create some hidden columns which count the number of "good", "bad" and "okay" values. Once you have this then it's simply a case of counting them rather than actual cell colours themselves. Of course if you change the conditional formatting criteria then you need to change the criteria in the hidden cells too!

[LPChip]

@Richard, yes I would too, but thats not the question that is asked here.

[datatoo]

The suggestion to use vba is probably best. Realize you can save macros in your own PERSONAL.XLSB file and not in the file you are working with, but still call those macros to do things you want

[Richard]

@LPChip, The point I was making is that a combination of the coloring and the hidden columns would give the impression that the excel sheet is counting the number of coloured cells without the use of VBA - which was the question being asked.

[datatoo]

I saw what you meant but also saw the reason given for not using vba was because the file had to be saved as xlsx. You can still use macros without that constraint, and if their is not reliable criteria for determining color.