Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Use Cell Color In Formula Without VBA

  1. #1
    Join Date
    24th January 2005
    Posts
    19

    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,
    Dave

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,042

    Re: Use Cell Colour In Formula Without Vba

    Nope.
    Regards,

    Wigi

    Excel MVP 2011, 2012, 2013



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

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

  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

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

    Forum Rules | Message to Cross Posters | How to use Tags

  4. #4
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2005
    Posts
    19

    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.
    Regards,
    Dave

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    9th February 2012
    Location
    Pittsburgh, PA
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,042

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

    Wigi

    Excel MVP 2011, 2012, 2013



    For more Excel memes: visit http://www.wimgielis.be ==> English articles ==> Excel memes

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

  8. #8
    Join Date
    9th February 2012
    Location
    Pittsburgh, PA
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    9th February 2012
    Location
    Pittsburgh, PA
    Posts
    4

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

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

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Color Code Cell Formula References
    By UNIQUO in forum EXCEL HELP
    Replies: 5
    Last Post: December 3rd, 2007, 15:00
  2. Color Cell If Formula Overwritten
    By mark smith in forum EXCEL HELP
    Replies: 2
    Last Post: November 1st, 2006, 06:42
  3. Use Function/Formula to Color Interior of Cell
    By akaster in forum EXCEL HELP
    Replies: 5
    Last Post: April 20th, 2006, 02:36
  4. Clearing cell contents by color or formula
    By viper in forum EXCEL HELP
    Replies: 3
    Last Post: April 2nd, 2006, 17:36
  5. filling a cell color within a formula
    By rcavaliere in forum EXCEL HELP
    Replies: 2
    Last Post: February 9th, 2003, 01:37

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno