Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Excel Function Based On Cell Color

Posted on 2011-10-07
Medium Priority
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 81

Expert Comment

ID: 36934616
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.

Expert Comment

ID: 36938323
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 42

Expert Comment

ID: 36940565
You might be interested in this solution:


LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 36940612
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

ID: 36943238
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 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