Excel Function Based On Cell Color

Posted on 2011-10-07
Last Modified: 2012-05-12
is there a way to change the value of a cell based on the cell color of another cell?

Example:  If I have the following (fig1), then I set E1's fill color to red, i then want the x's to change to W's(fig2)

     a     b     c     d     e
1   x             x    x

     a     b     c     d     e
1   W           W   W    RED

Question by:darrennelson
    LVL 80

    Expert Comment

    Automatically changing the value in a cell based on the color in another cell is not possible with either worksheet formulas or VBA code. It would be possible to change the values if you are willing to run a macro.  The macro is a lot easier to write if the red color results from the user manually changing cell color (as opposed to Conditional Formatting).  The macro will also depend on the version of Excel that you are using.

    The better solution, however, may be to change the color of E1 because you change the value of cell A1 to W.  You can do this using Conditional Formatting and a Formula criteria like $A1="W"

    Cells C1 and D1 could change to W either using worksheet formulas or a Worksheet_Change event macro.
    LVL 4

    Expert Comment

    Changing the colour manually does not trigger any event in excel that i know of. I would like to know why you want to changing the colour to control something instead of the other way around.

    If the colour controls it you should technically have to decide what colour red will do it too, or a range of redish colours will need to be defined. what if the usual red rgb(255,0,0) is accidentally changed by a user to say rgb(255,5,5) which will probably look about the same but do you still call it red for the calculation purpose?
    LVL 41

    Expert Comment

    You might be interested in this solution:


    LVL 41

    Accepted Solution

    Applying your simple rule (if E1 is changed to red, then change all the X's in Range A1:D1 to W's), just open the attached and give it a shot.  The "simulated" event is generated when you select any cell after having changed E1 to Red.  If E1 is being set to Red as a result of conditional format change, or other event that is not making selections on the sheet, or activating another tab on the workbook, it won't work (re: a rewrite might be required to hook onto something else).

    I'm curious as well, what you're trying to accomplish?  Do you need the W's to change back to X's if the color in E1 is changed from Red to some other color?

    Note, the solution depends on the UNDO stack, and as a result if you're keen on keeping the ability to UNDO, then running any macro that affects data in the sheet blows the UNDO stack.

    See attached... Play with it to determine if this is what you'd like to do and let me know if further enhancement is desired.



    Author Closing Comment

    haha, it's a simple football pool.  I'm tinkering with your file, dlmille, and it does exactly what im trying to accomplish.  Now I just have to figure out you did that.

    basicall, I have each matchup in adjacent columns.  I enter everyone's picks with an X.  On monday, I want to highlight the winning team and have all the correct picks change to W's.  Then another formula totals everyone's wins.

    it's total overkill for what I'm doing, but I can never leave anything 'as is'....


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now