+ Reply to Thread
Results 1 to 7 of 7

Conditional Formatting based on Colours (or Values) on another Column

  1. #1
    Registered User
    Join Date
    05-29-2017
    Location
    London, England
    MS-Off Ver
    Microsoft Office Excel 2007
    Posts
    28

    Conditional Formatting based on Colours (or Values) on another Column

    The attached spreadsheet shows the Marks and % Deviation, both input from another sheet through Formulae only, not as absolute values.

    The Marks column is conditionally formatted to show different ranges, whereas the % Deviation column is conditionally formatted to show the Lowest 3 values.

    Only for the Lowest % Deviations highlighted, I wish to look up the colours in the Marks column, and based on it, I wish to highlight a cell in M column against the Lowest % Deviation (at M10 and M22 in this case) as below:

    (i) If the colours in the 3 cells are equal, I wish the highlighted cell in M column to be Red;
    (ii) If the colours in any 2 cells are equal, I wish the highlighted cell in M column to be Green; and
    (iii) If the colours in the 3 cells are different, I wish the highlighted cell in M column to be Blue;

    I am referring to colours above for easy identification, please note the values are formula-based.

    How do I proceed in order to automatically highlight the cell in the M column?

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    05-29-2017
    Location
    London, England
    MS-Off Ver
    Microsoft Office Excel 2007
    Posts
    28

    Re: Conditional Formatting based on Colours (or Values) on another Column

    I wonder such Conditional Formatting, based on Colours in another Column, may not be possible in Excel?

  3. #3
    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,019

    Re: Conditional Formatting based on Colours (or Values) on another Column

    1. Please wait at least a day before "bumping" your thread

    2. Those colors dont really exist in those cells, they are there because of CF only
    3. Formulas work on data, not formatting, they cannot "see" colors.
    4. You may be able to apply similar rules that you have for CF, to the new process you are trying to do
    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

  4. #4
    Registered User
    Join Date
    05-29-2017
    Location
    London, England
    MS-Off Ver
    Microsoft Office Excel 2007
    Posts
    28

    Re: Conditional Formatting based on Colours (or Values) on another Column

    @FDibbins

    Thanks for the explanations.

    You have said, "You may be able to apply similar rules that you have for CF, to the new process you are trying to do".

    Can you please provide one single such formula, so that I might be able to expand it for the others?

    Also, a minor adjustment to the rules:

    (i) If the colours in the 3 cells are equal OR if the colours in the 3 cells are entirely different (that is 3 unique colours), I wish the highlighted cell in M column to be Red; and
    (ii) If the colours in any 2 cells are equal, I wish the highlighted cell in M column to be Green

    Thanks.

  5. #5
    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,019

    Re: Conditional Formatting based on Colours (or Values) on another Column

    What I meant was, that you already have a set of CF rules for Marks. Have you tried to adapt those same rules to your new requirements?

  6. #6
    Registered User
    Join Date
    05-29-2017
    Location
    London, England
    MS-Off Ver
    Microsoft Office Excel 2007
    Posts
    28

    Re: Conditional Formatting based on Colours (or Values) on another Column

    @FDibbins

    The set of rules in Marks column only refers to values in the same column. Whereas my new requirement involves the % Deviation column too, specifically to the lowest value in that column.

    Sorry, I am new in this area....

    Thanks.

  7. #7
    Registered User
    Join Date
    05-29-2017
    Location
    London, England
    MS-Off Ver
    Microsoft Office Excel 2007
    Posts
    28

    Re: Conditional Formatting based on Colours (or Values) on another Column

    I am surprised nobody here was able to / interested to solve this problem. I wonder why nobody even commented about the feasibility to solve it let alone providing a solution.

    Anyway, I got a paid solution, which is through using Tables.

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Conditional Formatting of one cell based on values in a column
    By hammer68 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-23-2016, 06:12 AM
  2. [SOLVED] Conditional Formatting - creating rules based on colours in cell list
    By milkychips in forum Excel General
    Replies: 5
    Last Post: 01-02-2016, 12:00 PM
  3. Conditional formatting based on other cell colours
    By Smally in forum Excel General
    Replies: 2
    Last Post: 07-03-2014, 06:40 AM
  4. Replies: 8
    Last Post: 05-07-2014, 04:57 AM
  5. [SOLVED] Conditional Formatting Rows based on Unique Values in Column
    By drjones5 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-13-2013, 04:27 PM
  6. Replies: 6
    Last Post: 11-07-2013, 02:13 AM
  7. conditional formatting based on a series of column text values
    By stewphil in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-19-2012, 03:34 AM

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