# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  if statement based on cell's color

## luv2glyd

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.

----------


## Marcol

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.

----------


## DonkeyOte

> 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:





> 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)

----------


## Marcol

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

----------


## DonkeyOte

> 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...

----------


## luv2glyd

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

Can anyone tell me how to protect worksheet without losing functionality of macros. The project works perfectly until protected then some macros are thrown out, these usually being regarding  colour change. h

----------


## romperstomper

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.

----------


## Marcol

Hi aitch1952

Welcome to the forum.

You should not ask unrelated questions in someone elses' thread.

Please read the Forum Rules and then start your own thread, refer to this one if it is relevant to your problem by all means.

*Forum Rules*




> 2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.



If you do this I am sure your problem will be solved.

Cheers

----------


## romperstomper

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



He didn't because it isn't.  :Smilie:

----------


## DonkeyOte

> 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

----------


## romperstomper

> 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.  :Wink:

----------


## DonkeyOte

> 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).

----------


## romperstomper

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

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.  :Wink:

----------


## DonkeyOte

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 ?

----------


## romperstomper

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

----------


## romperstomper

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

----------


## shg

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.

----------


## romperstomper

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?  :Smilie:

----------

