Use Cell Color In Formula Without VBA

  • Hi,
    This is just a quick "can it be done" question.

    Is it possible to reference a cell's colour in a formula within a spreadsheet - without using VBA?

    i.e If Cell A1 has a fill colour of yellow, can I have a formula in cell A2 that says
    If Cell A1 fill colour = Yellow, result = 1 etc.

    I know this can be done in VBA, and I have done that, but just wondered if possible this way.

    Thanks & regards,

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

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


    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


  • Re: Use Cell Colour In Formula Without Vba

    Hi Parsnip,

    Thanks for your help - I haven't seen that before!

    AAE - I agree changing the design of the sheet would be better solution, but it was just a general point that arose from a specific example - ie can you reference any cell format properties in a formula withoutusing vba?

    Thanks again all.

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


  • Re: Use Cell Color In Formula Without VBA


    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.



    Excel MVP 2011-2014

    For more Excel memes: visit ==> English articles ==> Excel memes

    -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

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


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


  • 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

    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.


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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!