Excel 2007 - Constructing a formula for analysing color formatting data in a row

[Haldock]

Maybe some Excel champs can help me with my little dilemma for Excel 2007.

Background:
I've set up a spreadsheet in which I collect online test answers (sentences), arranged by row after who's taken the test. No numbers, just the full answers. A sort of teachers aid.

That data is then conditionally formated to color the cell (and text) GREEN if the text in it match the "right answer" for the question, and red if it does not. Standard conditional formatting stuff.

The issue:
I want to insert a column of cells next to all this, where a formula "analyses" the content of the row next to it to determine if the taken test is "approved or not approved (True/False thing) on the condition that the NUMBER of RED cells do not number more than TWO within the selection, and display "APPROVED/NOT APPROVED" according to this "auto correction function".

The tricky part is that there's no numbers involved in the answers, so it's dependant on Excel 2007 ability (or not) to check the color of the cells - a sort of "inverted" conditional color formatting.

Can Excel 2007 analyse the color of cells? I believe 2010 can, but I'm not sure about 2007. If it can, how would I go about constructing that formula?

[Haldock]

I made an example file that should make what I want to do clearer. See attached file.

[Haldock]

Update:
Playing around some myself I've discovered som... bizarre issues in Excel 2007.

I went sniffing around the internet some more, thinking that there must be someone else who've run into this dilemma and after som digging I found this page:
http://www.cpearson.com/excel/colors.aspx
Complete with a VBA file containing a bunch of useful looking functions, which I tested out.

Now, in that collection of commands I found the promising "=COLORINDEXOFONECELL" which should return a index value of the background color (if "FALSE" is included in the formula).

Testing it out against my auto-formating test-answers I found that it responded simply with "4" no matter which color the background had.

Okay I thought, maybe there's more to this? And surely there were, the default excel index only stores 56 color values - so a lot of "light-green" and similar doesn't display.

I set up a seperate cell row and played around with background color fills versus the =COLORINDEXOFONECELL function, suddenly I found that yes: It does work! Dark red returned the value "3" just as stated on that webpage.

You did have to update the formula each time because Excel does not register a color change as a prerequisite to auto-update a calculation. Fair enough.

I figured that okay, I can simply change the background color that the auto-formating fills in to a color that the index has, then I could potentially make a longer IF formula that solves my problem.

I did, but there was still no difference in the value displayed. Strange, perhaps the formula was broken? After fiddling around (I don't use a english version so certain formula things differ, such as ";" instead of ",") I confirmed that no, I've not made any mistakes in that part. It should work.

Perhaps it needed more experimentation, so I tried changing color fill manually and updating the formula, strangely enough it worked! The function cell now displayed a 3. Except well...

Changing fill color manually doesn't actually "override" the auto-formating color but somehow layers itself beneath THAT color - So the auto-formating that checks if the stated answer is correct, does not use the same color fill function that the manual does - and the =COLORINDEXOFONECELL only checks against the manual version, making the whole thing pretty useless. In other words, there are TWO background fill colors at work!

Possibly the auto-fill fills in some sort of general layer, because the formula displays a "4" and counts all cells with auto-fill as a "4" when summing up no matter what actual color is displayed (so a red, and a seperate green, cell both display as "4"). Checking against a normal, white, untouched cell merely displays a "1". Still, the two FILL functions appear to be seperated.

If I knew what kind of background fill that the auto-formating function uses and I could somehow point the formula towards checking that against the index I could probably get it to work...

Any ideas?

edit:
I appear to have found some more information, "ThemeColor" property appear to be the fill layer used by the auto-formating function (as described here).
Gonna experiment some with it.