+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting cells to light up based on two cells a list of values in them

  1. #1
    Registered User
    Join Date
    10-25-2016
    Location
    UK
    MS-Off Ver
    2013
    Posts
    19

    Conditional formatting cells to light up based on two cells a list of values in them

    Hi,

    I'm looking to conditionally format column AX7 based on whether column AU7 and AX7 contain any of the values of a list of values eg "A", "B", "C", "D".

    So, I would like column AX7 to light up red if columns AU7 and AX7 contain any of the aforementioned values. To give you an idea of what I am trying to accomplish, the cells already light up amber if any of those values have been entered once, which is pretty simple to do.

    I would like to do the same for column BA7 (based on columns AX7 and BA7).

    Is this possible?

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Conditional formatting cells to light up based on two cells a list of values in them

    I imagine that you are looking for a one formula solution, and hopefully you'll get one. However until that happens this helper cell solution may be useful. The helper cell is populated by the formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The conditional formatting rule would then reference the helper cell, XDF7 in this case, as follows: =XDF7=TRUE
    Attached is a demonstration using the cells A1, B1 and XDF1.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    10-25-2016
    Location
    UK
    MS-Off Ver
    2013
    Posts
    19

    Re: Conditional formatting cells to light up based on two cells a list of values in them

    Thanks for this.

    However, I can't seem to get it to work and have a lot of problems with it.

    Attached is part of what I'm trying to do. I've changed A, B, C, and D to UA, UB, UC and UD to simplify things (at least from my point of view and the conditional formatting).

    What I would like is if only the second cell with U in would turn red (not the first). Also if it could be extended for the entire range down to 25 in this case.

    Not sure if this is possible?

    Thanks,
    Jack
    Last edited by AliGW; 12-13-2016 at 09:52 AM. Reason: Attachment containing sensitive data removed at OP's request.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Conditional formatting cells to light up based on two cells a list of values in them

    I think that I understand what you want. If A2 and B2 both contain one of the U values then B2 should highlight, and if A2 and C2 both contain one of the U values then C2 should highlight. I am guessing that if A2, B2 and C2 all contain one of the U values B2 and C2 should highlight. If that is the case then the attached file will do what you want. There are two helper columns, column E having the formula you already put there and column F having:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    The conditional formatting rule that is applied to B2:B25 is =E2=TRUE and the CF rule applied to C2:C25 is =F2=TRUE
    Let us know if you have any questions.
    Attached Files Attached Files

+ 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 based on Multiple Values of other Cells
    By Sovrin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-03-2016, 08:10 PM
  2. Conditional formatting, based ot multiple values in 2 cells.
    By kaosbg in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2014, 02:47 PM
  3. Replies: 2
    Last Post: 06-20-2014, 10:48 AM
  4. Replies: 6
    Last Post: 06-03-2014, 08:40 PM
  5. Conditional formatting a cell based on values in two different cells
    By srmp07 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2013, 07:54 PM
  6. conditional formatting based on values of two dfferent cells
    By bonzerstuff in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-19-2013, 07:38 AM
  7. [SOLVED] Conditional Formatting Based on Adjacent Cells and Values
    By stusic in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-03-2013, 03:59 PM

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