+ Reply to Thread
Results 1 to 5 of 5

Detecting 2 substrings in cell with conditional formatting.

  1. #1
    Registered User
    Join Date
    06-03-2017
    Location
    Amsterdam
    MS-Off Ver
    Office2016
    Posts
    3

    Detecting 2 substrings in cell with conditional formatting.

    Hello All,

    Not sure if possible but would be helpful if it is.
    I need conditional formatting to check if cells contain two substrings together.
    for example the following combinations are not allowed together:
    (RFL & ROX), (RCM & RFW), (ELI & RNG)

    A1| RFL.ROX.CAO / BLABLA
    A2| BLABL - THIS AND THAT
    A3| ELI OHG/CM/15A | RFL
    A4| IND/CM/15A & RCM/RFL.RFW

    Cells A1, A2 and A4 should turn red because each of them contains a combination of 2 values that are not allowed together.
    It is unfortunately not allowed to use VBA or use helper columns in the document I need it for
    Any ideas?

  2. #2
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Detecting 2 substrings in cell with conditional formatting.

    ok Try with a helper column

    Try

    B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

    Now select the A1:A4 and press Alt-O-D -> New rule->> Choose last one->>> put formula =$B1=TRUE ->> then select the format then ok ok and ok.

    Check the attached file.

    Hope this help.
    Last edited by shukla.ankur281190; 06-03-2017 at 04:03 AM. Reason: Attachment missing
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,892

    Re: Detecting 2 substrings in cell with conditional formatting.

    Formula for CF:

    =SUM(COUNTIF(A1,{"*RFL*ROX*","*ROX*RFL*","*RCM*RFW*","*RFW*RCM*","*ELI*RNG*","*RNG*ELI*"}))>0

  4. #4
    Registered User
    Join Date
    06-03-2017
    Location
    Amsterdam
    MS-Off Ver
    Office2016
    Posts
    3

    Re: Detecting 2 substrings in cell with conditional formatting.

    Thanks shukla.ankur.
    This works, I hoped for a solution without a helper column.

  5. #5
    Registered User
    Join Date
    06-03-2017
    Location
    Amsterdam
    MS-Off Ver
    Office2016
    Posts
    3

    Re: Detecting 2 substrings in cell with conditional formatting.

    Thanks Phuocam, your solution did ălso work!

+ 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. Match Cell Substrings to Column and Return Adjacent Cell
    By ashb444 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-06-2015, 11:24 AM
  2. Replies: 4
    Last Post: 02-20-2015, 09:40 AM
  3. Detecting if a cell has conditional formatting applied to it
    By emilyloz in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-11-2013, 01:37 PM
  4. [SOLVED] Match multiple substrings in a cell against a lookup table and return concatenated values
    By stevewc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-13-2013, 10:50 AM
  5. Conditional Formatting and detecting the state of a specific cell
    By sir.york in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-18-2013, 09:24 AM
  6. Replies: 6
    Last Post: 02-07-2013, 02:29 AM
  7. Detecting conditional formatting
    By RJH in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-06-2006, 02:15 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