if statement based on cell's color

Is it possible to detect a cell's color with out using VB? So for example can a formula similar to this be written: =if(color(A1)=red,1,2)

Thank you.

I think the short answer is no.

See this link for some VBa ideas
http://www.cpearson.com/excel/colors.aspx

[EDIT]
I'll reconsider that reply.
See this link, by their own admission it is limited, it seems to return the colorindex value.
http://www.ozgrid.com/forum/showthre...t=82173&page=1

A quick test suggests it doesn't automatically update.

Originally Posted by luv2glyd

A quick test suggests it doesn't automatically update.

Altering the format of a cell (other than by CF logic - ie calculation) is not a Volatile action therefore no recalculation of colour index will take place [edit: and FWIW CF colour is not colour index]
(same holds true for a UDF even it is set as Volatile).

The above is covered on the referenced web page from Chip Pearson's site:

Originally Posted by C.P

Excel does not consider changing a cell's color to be significant to calculation, and therefore will not necessarily recalculate a formula when a cell color is changed...
[VBA] functions use Application.Volatile True to force them to be recalculated when any calculation is done, but this is still insufficient.
Simply changing a cell color does not cause a calculation, so the function is not recalculated, even with Application.Volatile True

Using arbitrary formatting as means of differentiation is ill advised for this reason also (ie in addition to VBA requirement - GET.CELL is still VBA)

Thanks (yet again) Don

I just assumed Richard Schollar would have pointed out that his solution was in fact VBa.
The thread title was "Use Cell Color In Formula Without VBA"

I must confess I had not read Chips' webpage fully when I posted.


I'll reconsider that reply,on reflection.

The short answer is no.

[EDIT]
An updated workbook is available here
http://www.excelforum.com/excel-gene...ml#post2377147

Originally Posted by Marcol

I just assumed Richard Schollar would have pointed out that his solution was in fact VBa.

Take anything Richard Schollar says with a HUGE pinch of salt....
(just kidding of course - but it should be sufficient to prompt a response from him as and when he stumbles across this - he doesn't post as often as he used to!)

The old XLM calls from a Name will circumvent Enable Macros dialog etc prior to XL2007 but thereafter if you use them you must save in macro-enabled format.
(and you can't use on Mac XL2008 given lack of VBA support - at least NAFAIK)

Note: I like using the XLM stuff from Names as much as anyone I just think it's worth remembering that it is to all intents and purposes still code...

Originally Posted by Marcol

I just assumed Richard Schollar would have pointed out that his solution was in fact VBa.

He didn't because it isn't.

Originally Posted by D.O

GET.CELL is VBA

was not the best (or correct) turn of phrase, however, the XLM calls are Macros.

The fact that such calls will not run on 2008 and also require macro enabled format 2007+ would dictate that to some extent these calls are code, no ?

Perhaps you could offer a better synopsis ? [for future ref.]

edit: link with overview

http://j-walk.com/ss/excel/faqs/xl95faq1.htm

Originally Posted by DonkeyOte

was not the best (or correct) turn of phrase, however, the XLM calls are Macros.

Macro functions to be precise.

The fact that such calls will not run on 2008 and also require macro enabled format 2007+ would dictate that to some extent these calls are code, no ?

ah, but they will run in 2008. And they are code in much the same way that all functions are code when you get down to it.

Originally Posted by romperstomper

ah, but they will run in 2008

Can you elaborate ?

Most posts I've come across imply otherwise but I know better...

Should I or anyone else come across someone trying to run XLM calls in 2008 I would like to know what the trick is to run them if indeed a trick is required - not clear - and I've no Mac on which to test (more's the shame).

Marcol, thanks for the spreadsheet. Looks like I'll be going with a bit more in depth VB, but this setup may come in handy in the future.

aitch1952

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

No trick - you can run the macros via the macros dialog, and you can use the XLM functions in defined names as normal.

Edit: to add some detail for creating them (though it's the same as always, I think):

Insert a macro sheet (right-click a sheet tab, Insert..., choose Excel 4.0 macro sheet)
Enter a header name, then your functions in one column, then Insert-Name-Define, choose a name (usually the same as the header cell!) and make sure to check the Command option in the dialog.
Then run it via Tools-Macros...

Obviously you do need to save it in a macro enabled format.

edit: below composed pre-edit to above post but still curious...

so when it comes to saving the file with Names in place are you able to save in macro enabled format in 2008 ?

PS I can't believe you've made me defend Richard.

Yes, you can save in older formats or as xlsm or xlsb.

IMHO, XL4 macros should be shunned. They are neither fish nor fowl, akin to kissing your sister slightly unchastely. It's one thing to retain compatibility for legacy applications, but quite another to introduce them for new requirements. Think of that odd wrench you've owned for 20 years -- don't design something that needs it. Think Ada.

Methinks yon metaphor is inverted. We already have the old wrench and it's the only one that fits the machine. What do we do - wait and buy a new machine, or use the tools we have?