Determine cell color with formula?

[dasadler]

I could not find any formula to identify the color of a cell either by name or number. I did find something on the internet for a user defined function but it doesn't seem to work and I think something is missing.... here is the code:

Function ColorIt(CellRef)
ColorIt = CellRef.Interior.Color
End Function

When I used it, I filled cell A! with red then in cell b2 I entered =colorit(A1) expecting some value to be returned but instead it returned #NAME?

so what is it missing? or... is there a built-in Excel function to identify cell color that I missed?

[sdckapr]

Where did you put the function? The error suggests it may not be in a module of the workbook calling the code...

Steve

[dasadler]

I was in the workbook and hit alt-f11 and pasted it. It just shows as being in sheet one.

[sdckapr]

You may also want to check Allen Wyatt's Excel Tips at http://excel.tips.net/Pages/T010179_Det ... Color.html. This gives some code to separate out the Red, Green, Blue components of the Color value.

Steve

[sdckapr]

I was in the workbook and hit alt-f11 and pasted it. It just shows as being in sheet one.

In needs to be in a general module. You put it in as worksheet code.
When in VB (alt-f11), insert module, and paste the code into the module that is created.

Steve

[dasadler]

That worked... it returned 255. so what is the diff where the code goes? Other than it works or not.

[dasadler]

funny thing- i changed the color in A1 expecting the 255 to change but it did not. Any idea why not?

Actually, the value does change but only if I retype the formula (function). Hitting F9 to recalculate has not effect. So... it seems the function only works upon initial entry but does not update as a color will change.

[Rick Rothstein]

Hitting F9 to recalculate has not effect.

Hitting F9 updates the color value for me. The reason the function does not update automatically is because changing a cell color does not trigger any trappable events in Excel. The ColorIt function works similarly to the built-in CELL function in this way.

[dasadler]

I don't understand. You said hitting f9 works for you... I presume you do that after changing the color? Wonder why it does nothing for me? If I change the color then put my cursor in the formula bar then click the check mark or hit enter, it will update the color value.

[Rick Rothstein]

I don't understand. You said hitting f9 works for you... I presume you do that after changing the color? Wonder why it does nothing for me? If I change the color then put my cursor in the formula bar then click the check mark or hit enter, it will update the color value.

Yes, I hit F9 after changing the color and the displayed value changes. I'm using XL2003 right now, but the Excel version should not matter... F9 should update the formula. As a matter of fact, I tried changing a bunch of settings and nothing I did stopped F9 from updating the UDF's displayed value. There is only one possible thing I can think of... does your keyboard have a function key toggle button? If so, perhaps you hit it by accident and you F9 key is issuing the alternate functionality for that key rather then the Windows F9 functionality.

[sdckapr]

That worked... it returned 255.

Yes Red has a value of 255. the values will range from 0 (black) to 16777215 (white) and is red value + Green*256 +Blue*256*256

so what is the diff where the code goes?

For Excel to find the function it needs to be in a module. You placed it in the code for a worksheet, and it does not look there for the function. You place event-handling code in those objects.

Wonder why it does nothing for me?

One thing you may try if the F9 does not work adding the line:
Application.Volatile

at the start of the function....

Steve

[dasadler]

Thanks for all the help. I think I have a bad keyboard... it seems that it will work with F9 but I have to press it very hard. Time to replace the H/W, I think.

[Jim Cone]

My free "Determine Colors" Excel add-in displays the cell interior, border and font colors on the cell right-click popup menu.
Does both standard and conditional format colors - xl2003 and earlier only.
Other programs of mine at...
https://www.dropbox.com/sh/ttybwg5e9r31 ... Tcw4a?dl=0
'---
Jim Cone
Portland, Oregon USA

[dasadler]

Thanks Jim. Alas, I use Excel 2007... in any case, my desire was to test for cell color so my worksheet would insert a value into one cell based on the color of another.

[HansV]

so what is the diff where the code goes? Other than it works or not.

In the screenshot below, you see three modules in two categories, Microsoft Excel Objects and Modules.

x559.png

(There are other categories but we don't need to go into them now)

The two modules in the Microsoft Excel Objects category are of a special type. They are not intended to store general-purpose macros and functions. As you have found out, a macro or function created in such a module is unknown when you work in Excel.

The modules in this category are used for so-called event procedures. This is code that runs automatically in reaction to a specific event. For example, a Workbook_Open event procedure in ThisWorkbook is executed each time you open the workbook, and a Worksheet_Change event procedure in the Sheet1 module runs each time you change the value of a cell in Sheet1.

There will always be a ThisWorkbook module and a module for each worksheet and chart sheet in this category. These modules are created by Excel and cannot be deleted directly by you. (If you delete a sheet, Excel automatically removes the corresponding module.)

The other category, Modules, is used for general-purpose VBA code such as macros and user-defined functions. You can create and delete modules in this category yourself.

[dasadler]

Thanks for the explanation Hans.

[stuck]

Perhaps a bit late but the best info on working with colours in XL I've ever come across is Chip Pearson's pages:
http://www.cpearson.com/excel/colors.aspx

Ken

[HansV]

That is indeed an excellent resource!