+ Reply to Thread
Results 1 to 9 of 9

Conditional formatting a cell based on the text of another cell

  1. #1
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    38

    Conditional formatting a cell based on the text of another cell

    Hi Team,

    So I have Restaurant locations in column B a table in column I and an action item in column E.

    At the moment, if any cell in column B matches the table in column I, it highlights red.
    The formula for that works perfectly and is as follows:

    =NOT(ISNA(VLOOKUP(B1,$I$22:$I$31,1,FALSE)))

    Now I would like the above formula to factor in column E as mentioned previously to do ONE of the following:

    1) To do the formula above IF column E value = "911" or "Escalation"
    2) To STOP the formula above IF column E value = "Non-Escalation"

    Please let me know if you have any further questions! Thank you!
    Attached Files Attached Files
    Last edited by TSACov; 08-20-2020 at 10:14 AM.
    Thanks!
    - TSACov

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting a cell based on the text of another cell

    If this really is a Conditional Formatting rule, instead of..
    =NOT(ISNA(VLOOKUP(B1,$I$22:$I$31,1,FALSE)))
    your could probably just use...
    =VLOOKUP(B1,$I$22:$I$31,1,FALSE)
    Or just...
    =match(B1,$I$22:$I$31,0)

    As far as your actual question is concerned...
    you dont really indicate how col E relates to B or I?

    If B1 relates to E1, then perhaps this?

    =and (E1<>"Non-Escalation",match(B1,$I$22:$I$31,0))
    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

  3. #3
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    38

    Re: Conditional formatting a cell based on the text of another cell

    Hi Ford! Thank you very much for the response!

    I'm essentially writing up a faux project to do with simulated restaurant locations and case studies. Please see above that I have attached what I am doing.

    The relationship is as follows:

    Column B = Restaurant Location of where a case study is
    Column E = Action (what I do class the case study as: If I class it as an 'escalation', 'non-escalation' or '911'
    Table in Column I = All Restaurant Locations that are either considered High Risk or are in California.

    So to re-explain my question:
    The original conditional formatting formula: =NOT(ISNA(VLOOKUP(B1,$I$22:$I$31,1,FALSE))), looks at the locations in column B and tries to match the locations in the table in Column I. If there is a match, it highlights the cell in Column B.
    Now I wondered if it was possible to also include a relationship to Column I. For example, the above formula will only occur if the Location in Column B is matched with any locations in column I, AND ALSO has an action on either 'Escalation' or '911'. If it has an action item of 'Non-Escalation', there is no need for it to be highlighted.
    P.S If cells in the column are blank, I'll need them to remain blank and without a #N/A or something like that haha



    Just to give you context of what is already in the excel. If you do go into the excel you will find 3 conditional formatting formulas. One that highlights High Alert locations in Red. One that highlights California Locations in Orange. And one that highlights duplicates in column B as yellow. Each one is overridden respectively. I.E. If a cell in column B has a duplicate, is listed as a California restaurant AND is also a High Alert location, it will highlight in red. If a location meets none of these categories it simply stays a normal cell.

    Hope this clarifies it!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting a cell based on the text of another cell

    I just noticed that your CF vlookup rule references B1 instead of B2?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting a cell based on the text of another cell

    OK I see also that your CF range started in B1, instead of B2, always better to keep the range to what your data is.

    Having said that, my MATCH suggestion does seem to work...
    =MATCH(B2,$I$32:$I$60,0)
    =MATCH(B2,$I$22:$I$31,0)

    For your question though, try these...
    =AND($E2<>"Non-Escalation",MATCH(B2,$I$32:$I$60,0))
    =AND($E2<>"Non-Escalation",MATCH(B2,$I$22:$I$31,0))

  6. #6
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Conditional formatting a cell based on the text of another cell

    (A)
    I would like to report "hidden risks" in your data:
    table "High Alert" : I22:I31
    table "California" I32:I60
    There are some duplicate ID's (200,338,...) may caused a wrong counting (cell K21)
    for instant, If C200 appeared in column B, is is counted twice
    Offer in K21:
    Please Login or Register  to view this content.
    (B) Back to your raising issue

    I approach from "yellow" first at cell B2
    =COUNTIF($I$22:$I$60,B2)=0
    Then second, orrange:
    =E2="Non-Escalation"
    Last, red:
    =COUNTIF($I$22:$I$31,B2)>0
    Attached Files Attached Files
    Quang PT

  7. #7
    Registered User
    Join Date
    03-18-2020
    Location
    Dallas, USA
    MS-Off Ver
    2020
    Posts
    38

    Re: Conditional formatting a cell based on the text of another cell

    based on your (A) answer - Absolutely fantastic! Thank you so much!

    based on your (B) answer - Your formulas are correct but I mustve miscommunicated which category they are for so all I had to do is apply the suggested formulas to which thing I wanted it to be for. Therefore, this is a solved thread Thank you so much! I was obviously making things more difficult than necessary for myself!

    Y'all have been great thank you!!!

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Conditional formatting a cell based on the text of another cell

    Quote Originally Posted by TSACov View Post
    Your formulas are correct but I mustve miscommunicated which category they are for so all I had to do is apply the suggested formulas to which thing I wanted it to be for.
    Nice to hear it works.
    Anyway, try to keep it simple. Try to keep you understand and confident:
    In priority order, if any condition met, it stop with assigned colour.

    1) Any ID's out of both lists: "yellow"
    =COUNTIF($I$22:$I$60,B2)=0

    2) If not, ID is obviously located in HA or CA
    But if ID should not be HA, if E2="Non-Escalation", so it is in CA
    "orrange"
    =E2="Non-Escalation"

    3) Lastly, "red" if ID belongs to HA range:
    =COUNTIF($I$22:$I$31,B2)>0

    Stop me if something goes wrong.

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Conditional formatting a cell based on the text of another cell

    happy to help and thanks for the feedback

+ 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 Text in Another Cell
    By femola in forum Excel General
    Replies: 3
    Last Post: 01-21-2016, 06:16 AM
  2. VBA Conditional Formatting based on cell value and Text
    By uroy67640 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-09-2015, 01:38 AM
  3. [SOLVED] Conditional Formatting based on a Date Cell and a text Cell.
    By Clark85 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-09-2013, 07:53 AM
  4. [SOLVED] Conditional Formatting Based on Another Cell's Text
    By computergeek in forum Excel General
    Replies: 6
    Last Post: 06-26-2012, 11:14 AM
  5. Replies: 1
    Last Post: 03-21-2012, 03:27 PM
  6. Replies: 2
    Last Post: 11-23-2010, 11:13 PM
  7. Replies: 4
    Last Post: 07-31-2009, 08:47 AM

Tags for this Thread

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