Is it possible to return a value based on cell format?

**blaidan** · 08-09-2007, 03:35 PM

I don't even know if this is possible but can you use some formula within Excel to return a value to a cell based on the cell formatting of another cell? Here is specifically what I am trying to do:

Cell A2 has a value of 10. If the font color of that cell is black (or "automatic") I want a formula in cell A1 that will return a value of "+10". If the font color of cell A2 is red, I want the formula in cell A1 to return a value of "-10".

Is this possible? Thanks in advance for any help as I am absolutely stumped.

**FrankBoston** · 08-09-2007, 05:19 PM

To play with font colors in worksheet functions, you need a way to get them.
The following User Defined Function should give you the hook to do what you want. Install it into a Module for your workbook.

Please Login or Register  to view this content.

**blaidan** · 08-09-2007, 06:12 PM

Thanks for the reply, FrankBoston!

I have to admit that I am very much a noob when it comes to Excel usage greater than the most basic functionality. Is there any way that you can walk me through what I need to do and how to ultimately use this user-defined function to achieve my goal?

Thanks again!

**blaidan** · 08-09-2007, 07:17 PM

OK, I think I figured out how to create the user-defined function in a module and use that module in my workbook.

However, I have a question on how to use it. Using my example from my first post, cell A2 has a value of "10" and the font color of that cell is red.

Cell A1 has the formula "=IF(GetFontColor(A2)=?????,-10,10)" where ????? is equal to what?

I am unsure what the value should be so that if A2 has a font color of red then the value returned in A1 would be "-10" and if the font color in A1 is black or automatic then the value returned in A1 would be "10".

Any thoughts?

**blaidan** · 08-09-2007, 07:59 PM

Alright, I did a little more research on this subject. If I am understanding the ColorIndex correctly, then putting a value of -4105 into my IF() should give me the correct answer, right?

So, if taking my example.......

Cell A2 has a value of "10" and the font color of that cell is red.

Cell A1 has the formula "=IF(GetFontColor(A2)=-4105,-10,10)" where -4105 is the value for text that has not been assigned a color and therefore appears to be black ("automatic"?)

This seems to yield the correct answer in cell A1 of "-10". However, if I change the color of A2's font to black or automatic the value in A1 does not change to "10" automatically as I would expect. I checked to make sure that automatic formula recalculation was on and it was. I also tried to manually force a recalculation but that also did not change the value to what it should have been.

Only deleting the formula from A1 and repasting it into the cell yielded the recalculation and the proper value of "10".

Am I still doing something wrong?

**blaidan** · 08-09-2007, 08:20 PM

OK, last time. I think I have what the formula should be....

=IF(GetFontColor(A2)=3,-10,IF(GetFontColor(A2)=-4105,10))

Although, I suppose that =IF(GetFontColor(A2)=-4105,-10,10) should yield the same results.

Seems to yield the correct values.

However, I am still stuck with the issue of recalculation. It won't automatically recalculate. It won't manually recalculate with an ALT+F9. It will only recalculate with a complete deletion and repasting of the formula into the cell.

Kinda defeats the purpose of the automation I was trying to accomplish. Any thoughts?

**blaidan** · 08-10-2007, 10:40 AM

Found a good site (http://www.cpearson.com/excel/colors.htm) that outlined alot of VBA stuff that I could use to achieve my goal.

He does mention that there seems to be no way to automatically recalculate the formula when a formatting change is made because excel does not see a format change as an actual change to the cell in order to force the auto-recalc. He does say that doing a manual recalc should work. However, I can't get it to pick up a change even doing a manual recalc (ALT+F9).

He mentions trying to use the Worksheet_SelectionChange event procedure to force a calculation. I tried to implement this but either I did it incorrectly or it didn't work.

Anyone have any ideas to get the recalc to work (either automatically or manually) if I change the format color of a cell?

**FrankBoston** · 08-10-2007, 11:28 AM

About ColorIndex values. I'm confused about the value -4105 also. Your investigation would be as good as mine.

Excel seems to recalculate a function only if it refers to a result VALUE in another cell, or if the code does. But there is the Application.Volatile VBA call that tells Excel to recalculate anyway.

I have inserted this into my code above. I haven't tested this, but I hope it does the job.

**blaidan** · 08-10-2007, 11:41 AM

Hello FrankBoston -

If you go to that website that I listed it mentions the following:

"It is important to remember that if a cell has no color assigned to it, and therefore appears to be white, the ColorIndex is equal to the constant xlColorIndexNone, or -4142. It does not equal 2, the default ColorIndex value for white. Similarly, text that has not been assigned a color, and therefore appears to be black, has a ColorIndex value equal to the constant xlColorIndexAutomatic, or -4105. It does not equal 1, the default ColorIndex value for black."

This does work within the code and returns the proper value.

As for your reply, you are correct in that since a font color change is not recognized as a VALUE change to the cell, Excel does not recalculate the function/formula. However, a manual recalc should be able to be accomplished but I can't seem to get even a manual recalc (ALT+F9) to work.

I am not familiar with the implementation of the Application.Volatile VBA call that you mention. Can you please eleaborate on it's usage and how I might be able to use this to force a recalc when I change the font color of a cell?

Thanks!

**FrankBoston** · 08-10-2007, 12:07 PM

Look again at my code posted before in this thread (Post #2).
I added Application.Volatile in the way it should appear.

Thanks for the info about the colorindex. Could Excel be more complicated?

**blaidan** · 08-10-2007, 12:38 PM

Ah, sorry. I didn't notice that you edited the code above. Thanks. I'll give this a try. And, yes, I agree. Excel can be complicated indeed!

**blaidan** · 08-11-2007, 03:01 PM

Ok, I tried the Application.Volatile but it didn't force the recalculation. However, I did a little more research and found that if I do a full maunal recalculation by doing CTRL+ALT+F9 then it will force a recalculation when I change the font color of a cell.

Apparently, when you do a normal manual recalculation (ALT+F9) Excel only recalculates those cells that it determines need to be recalculated. Doing a full manual recalculation (CTRL+ALT+F9) forces Excel to recalculate everything whether Excel thinks it needs to be recalculated or not.

Hope my issue and research helps someone else who might come across the need for something like this.

Also, FrankBoston, thank you very much for your suggestions and help as well!

**Sean Anderson** · 08-11-2007, 07:31 PM

The attached works for me. You want to press F9 not ALT+F9 to manually recalculate.

**blaidan** · 08-12-2007, 11:16 AM

Originally Posted by Sean Anderson

The attached works for me. You want to press F9 not ALT+F9 to manually recalculate.

I guess I should've included the fact that I tried F9 as well and it still would not recalculate my font color change.

I'm glad that F9 works for you. May have something to do with the older version of Excel that I'm using. In any event,a s long as I can get the manual recalc forced by doing the CTRL+ALT+F9 then I'm happy!