+ Reply to Thread
Results 1 to 22 of 22

Conditional Formatting based on the fill color of a cell

  1. #1
    Registered User
    Join Date
    04-05-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Conditional Formatting based on the fill color of a cell

    I have searched high and low for an answer to this and I'm beginning to think it can't be done.

    I am trying to format a cell fill color based on another cell's fill color. Simple right...?

    Any help would be appreciated.

  2. #2
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    re: Conditional Formating based on the fill color of a cell

    Hi,

    It is feasible ... but an easier solution would be to rely on the conditions (if any) you have used to color the first cell ...

    If the first cell is "manually" colored, then you need to get into VBA ...

    HTH

  3. #3
    Registered User
    Join Date
    04-05-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Conditional Formating based on the fill color of a cell

    Crap, That is what I was afraid of. The first cell is colored manually.

  4. #4
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Conditional Formating based on the fill color of a cell

    Still a little bit of hope ... with the numbers of different colors you are depending on, in the first cell ...

  5. #5
    Registered User
    Join Date
    04-05-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Conditional Formating based on the fill color of a cell

    Quote Originally Posted by JeanRage View Post
    Still a little bit of hope ... with the numbers of different colors you are depending on, in the first cell ...
    Not sure I understand what you are sying here. Sorry.

  6. #6
    Registered User
    Join Date
    03-28-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    29

    Re: Conditional Formating based on the fill color of a cell

    Hey mfrymyer,

    I believe the question is why do you set the colors you do. What is the criteria that makes you choose one color over another.
    Warm regards,

    Shampoo Monkey

    If you are happy with the results, please add to my reputation by clicking the icon next to the Post # in the bar above this post.

  7. #7
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Conditional Formating based on the fill color of a cell

    As a first step, dealing with colors, take a look at Bob's UDF function ...
    http://xldynamic.com/source/xld.ColourCounter.html

    HTH

  8. #8
    Registered User
    Join Date
    04-05-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Conditional Formating based on the fill color of a cell

    Quote Originally Posted by Shampoo Monkey View Post
    Hey mfrymyer,

    I believe the question is why do you set the colors you do. What is the criteria that makes you choose one color over another.
    It is a "purely manual" thing. I am a project manager and I have a need to be able to turn a cell to either yellow, red, or green based on whether a different cell's fill color has been changed "manually".

  9. #9
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Conditional Formating based on the fill color of a cell

    How many potential colors could you end up using for the "first" cell ... ?

  10. #10
    Registered User
    Join Date
    04-05-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Conditional Formating based on the fill color of a cell

    Quote Originally Posted by JeanRage View Post
    How many potential colors could you end up using for the "first" cell ... ?
    3. Either Green, Yellow, or Red

  11. #11
    Registered User
    Join Date
    04-05-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Conditional Formating based on the fill color of a cell

    Quote Originally Posted by JeanRage View Post
    As a first step, dealing with colors, take a look at Bob's UDF function ...
    http://xldynamic.com/source/xld.ColourCounter.html

    HTH
    Thanks. I tried Bob's UDF function, but I'm not much of a programmer and I probably screwed it up cause it didn't work.

  12. #12
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Conditional Formating based on the fill color of a cell

    If you are only using 3 basic colors for your reference cell ...
    does it mean your objective for the target cell is just to have the same color as the reference cell... ?

  13. #13
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Conditional Formating based on the fill color of a cell

    See attached file ...

    HTH
    Attached Files Attached Files
    Last edited by JeanRage; 04-05-2010 at 03:53 PM.

  14. #14
    Registered User
    Join Date
    04-05-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Smile Re: Conditional Formating based on the fill color of a cell

    Quote Originally Posted by JeanRage View Post
    If you are only using 3 basic colors for your reference cell ...
    does it mean your objective for the target cell is just to have the same color as the reference cell... ?
    Yes. That is absolutely correct.

  15. #15
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Conditional Formating based on the fill color of a cell

    ... OK then ... is the file attached answering your requirement ..?

  16. #16
    Registered User
    Join Date
    04-05-2010
    Location
    Kansas
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Conditional Formating based on the fill color of a cell

    Quote Originally Posted by JeanRage View Post
    ... OK then ... is the file attached answering your requirement ..?
    Unfortunately no. I do not want to have to enter something in the cell, rather just the changing of the fill color to trigger the change. I think this is one of those things I'm going to have to live with. I certainly appreciate your time and effort.

  17. #17
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: Conditional Formating based on the fill color of a cell

    If you do not need any input, use following and the cell contents will be cleared ...
    Please Login or Register  to view this content.
    HTH

  18. #18
    Registered User
    Join Date
    11-25-2014
    Location
    Liverpool, England
    MS-Off Ver
    2007
    Posts
    1

    Re: Conditional Formatting based on the fill color of a cell

    Hi there, I've noticed this thread and found it particularly interesting. However, not being an Excel expert (by a long shot!) I don't fully understand it.

    I too have two tables of data on the one worksheet. I'm using conditional formatting rules on the first table to determine the colour of the cells (red, green and yellow). This part is working fine.

    However, I need the second, almost identical table to replicate the colours cell by cell. So, if a specific cell in table 1 is red the same cell (in the relative position) in table 2 also needs to be red. Is there a way of doing this? The file attached seems to be doing the right thing but I have no idea how to implement a macro.

    Appreciate your help!

    ian.

  19. #19
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,011

    Re: Conditional Formatting based on the fill color of a cell

    jumpinjohnson, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    (Perhaps try to apply the same CF rules to the 2nd table, but base them on the 1st table?)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  20. #20
    Registered User
    Join Date
    01-29-2015
    Location
    turkey
    MS-Off Ver
    2010
    Posts
    1

    Re: Conditional Formatting based on the fill color of a cell

    hello friends,
    i was trying to make exactly same; "conditional format a cell, based on another cell's fill color"
    but could not create it according to previous steps.
    can any of you explain it simply please?

  21. #21
    Registered User
    Join Date
    12-16-2015
    Location
    Phoenix AZ
    MS-Off Ver
    2013
    Posts
    2

    Re: Conditional Formatting based on the fill color of a cell

    Awesome stuff, works great. Could this be changed to work for multiple columns? and Possible from another sheet, and even further, transpose the layout?
    I have 2 client that demand the same spreadsheet be formatted their own preference. I need 1 sheet formatted left to right, but the other client wants to see the same info top to bottom. I already have my formulas transposed, but when we color cells, it's a pain to get that over. thanks in advance for any help.

  22. #22
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,011

    Re: Conditional Formatting based on the fill color of a cell

    Dever, welcome to the forum

    Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1