Use Cell Color In Formula Without VBA

Re: Use Cell Colour In Formula Without Vba

Nope.

Re: Use Cell Colour In Formula Without Vba

As Wigi said, you can only reference the interior color of a cell via VBA.

However, there is a possible VERY LIMITED workaround you can try using a helper column and conditional formatting. Conditional Formatting is limited to three conditions, so this idea is limited to three colors.

Using column A as the helper column, enter the color name into cell A1. Next Conditionally Format B1 using the "Cell Value Is" and "Equal To", value of 1. Set fill color to Yellow.
In cell B1, use this formula: =IF(A1="Yellow",1,"")

As I said, this is a very limited workaround.
BTW, why do you need to know the color of the cell to get a certain results. You probably need a different approach to accomplish what you are doing.

Re: Use Cell Colour In Formula Without Vba

Hi Dave

There is a way to do it that uses the XLM4 Macro function GET.CELL (it is a little bit limited though):

1. Go Insert>Name>Define and name this formula that you are about to create "color"
2. In the Refers To box type:

=GET.CELL(63,OFFSET(INDIRECT("RC",FALSE),-1,0))
3.Back in the sheet, in the immediately adjacent cell below the one where you want to determine the fill colour type (so yellow cell in A1, then you enter the following in A2):

=color

This will return the colorindex of the cell interior (6 is bright yellow in a default installation).

So you can use this in a formula to do as you wish eg:

=IF(color=6,1,"It ain't yellow")

Best regards

Richard

Re: Use Cell Colour In Formula Without Vba

I am activating this thread after much delay because I have a similar issue here.

In my case I am using it as debugging method of my code. What I am doing is I have say column of cell that have various colored interiors.
At 3 columns over, I am attempting to create a worksheet function to retrieve the color value of color cell for that row. In the previous
column I was able to do this for the cells interior color index just fine. (Note: I just hate .offset() as I can not seem to keep indices straight)

Below are the two functions I create in VBA:

Function CellColorIndex(r As Integer, c As Integer) As Integer
CellColorIndex = ActiveSheet.Cells(r, c).Interior.ColorIndex
End Function

Function CellColor(r As Integer, c As Integer) As Integer
CellColor = ActiveSheet.Cells(r, c).Interior.Color
End Function

Ideally I would like the result in hex as CellColor = Hex(ActiveSheet.Cells(r, c).Interior.Color) though when I use CellColor() in a cell on the worksheet
I get some form of referencing error "I am not catching". I am currently not catching anything.

Instead of the bold line of code I have also tried the following (none of which works -- same error I suspect)

1. CellColor = ActiveCell.Offset(r, c).Interior.Color

2. ActiveCell.Offset(r, c).Activate
CellColor = ActiveCell.Interior.Color

3. ActiveCell.Offset(r, c).Select
CellColor = Selection.Interior.Color

Google Documentation shows these as working. When I put 1, 2 or 3 in the Immediate Window they retrieve the correct value.
Hmm??? When I debug the worksheet function it errors out - popping me out of debug at the line of code with .Interior.Color value.

So why is it that the .Interior.ColorIndex property is useable within VBA called as a WorksheetFunction yet .Interior.Color property
is not???

This inconsistency in Microsoft in use of VBA is what most offends me. Any ideas would be most appreciated.

maddog.

Re: Use Cell Color In Formula Without VBA

Maddog,

Welcome to Ozgrid.

Please do not hijack other's topics, even more such an old topic.
If the same subject is treated again, just start a new topic. You can reference the older topic with an URL.

Also, please use [CODE] tags for any VBA code you put over here.

For your function, Integer is too limited to catch the colors. Try Long instead of Integer.

Re: Use Cell Colour In Formula Without Vba

Ahh, I see my flaw (though I would have like it if Microsoft in their infinite wisdom had informed me that I had an overflow!).

My function should be returning a Long instead of an Integer

Function CellColor(r As Integer, c As Integer) As Long
CellColor = ActiveSheet.Cells(r, c).Interior.Color
End Function

So now this works. However in VBA the function Hex is not working though I may be able to figure that out, so I withdraw my question.
Thanks anyway.

maddog.

Re: Use Cell Color In Formula Without VBA

I did not mean to "hijack" (whatever that means to you). My intention was not to create a new thread since this question was so similar.
If that was a problem, I am sorry. I will start new threads...

I missed it also about [CODE] tags which I will do from now on. Thanks for the advice. I do not wish to ruffle feathers.

maddog.

Re: Use Cell Color In Formula Without VBA

Thread hijacking is when YOU ask questions in threads started by others.
The Forum Rules require you start your own thread.

Please edit your previous post and add the code tags.

Re: Use Cell Color In Formula Without VBA

I think I should comment that cell formulas (UDF or otherwise) that refer to color have an enherent flaw.
Changing a cell's color doesn't trigger calculation. Even if its a volatile function. One must manually calculate the sheet to insure that the results are accurate.

Re: Use Cell Color In Formula Without VBA

AAE,
Please excuse my newbieness. I do not know how to get an edit button on the posts you are
asking me to edit. Please explain how and I will.

maddog.

Re: Use Cell Color In Formula Without VBA

Below each post in every thread is an EDIT POST button.
Find your posts and click the button - it is on the same line of as Reply and Reply With Quotes.

You must be logged in, of course.