=Cell("color",A1) Error

Question

I am attempting to use the Excel cell function using the info_type color to return a 1 if the cell is color and a 0 otherwise.

=Cell("color", A1)

Should return 1 if A1 is colored and 0 if not.  From the Excel Help:

"color" The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).

Problem is:  =Cell("color", A1) only returns a 0 regardless of the color of A1.

Any ideas?

Answer 1

The formula returns 1 if the Number Format of cell A1 has a section for negative values with [Red], e.g.

$ #,##0.00;[Red]$ #,##0.00

It does not matter whether the value in A1 is positive or negative, or what color A1 has been set to in the Font or Fill tabs of the Format Cells dialog.

Answer 2

The formula returns 1 if the Number Format of cell A1 has a section for negative values with [Red], e.g.

$ #,##0.00;[Red]$ #,##0.00

It does not matter whether the value in A1 is positive or negative, or what color A1 has been set to in the Font or Fill tabs of the Format Cells dialog.

Somewhat bizarrely that function also returns a 1 if A1 has this format:

[Red]$ #,##0.00;$ #,##0.00

or

[Red]$ #,##0.00;[Blue]$ #,##0.00

but not this:

[Red]$ #,##0.00

nor this:

$ #,##0.00;$ #,##0.00;[Red]0

It seems to check - does [Red] appear in the custom format before the negative part of the format, which is not as described. Now, I accept that the alternative of positive numbers in red would be an odd choice, but strictly the function does not do what it says and could be misleading.

---

Adam Vero MCT, MMI, MBMSS:CRM 4, MCSA:Messaging Meteor IT - Training and Consulting Services