+ Reply to Thread
Results 1 to 10 of 10

Exclude specific text in cell string from highlighting

  1. #1
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Exclude specific text in cell string from highlighting

    Hi

    OP can be found here, and thanks to MickG for solving my original problem.
    https://www.excelforum.com/excel-pro...g-in-cell.html

    As my workbook has been put in to use there are a couple of issues that have become apparent.

    Please Login or Register  to view this content.
    This code by Mick does exactly what I needed and highlights the row if the number prefixed with "A" doesn't match 10 digits.
    I don't want this to change as I still need to highlight incorrect reference numbers.

    However, I have found people referencing specifically to "A2", so this is also highlighting as it has prefix "A" but only one digit. So previous code will highlight it red.

    Is there a way of keeping the original code so that reference numbers with prefix "A" that don't have 10 digits highlight red but if the string in cell contains specifically "A2" then it doesn't highlight?

    I hope that makes sense.

    TIA
    Tez

  2. #2
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Exclude specific text in cell string from highlighting

    Is "A2" entered in a cell as word/text or is it entered as a cell reference "=A2"

    OK shot in the dark assuming there is a textpart "A2" in a string

    add a little bit to the first line you are showing

    Please Login or Register  to view this content.
    Last edited by Roel Jongman; 11-23-2019 at 05:53 PM.

  3. #3
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: Exclude specific text in cell string from highlighting

    Thanks for your quick reply

    Well your shot in the dark was bang on target, however...

    Cell containing text string "A2" doesn't now highlight row which is what I needed, but other rows that contain cell with incorrect reference number also doesn't highlight

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Exclude specific text in cell string from highlighting

    Replace the last And with Or, does that do the trick?

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: Exclude specific text in cell string from highlighting

    Ahh, that does something weird.

    I'll put together some sample sheets with OP code and your suggested and post so that you can see whats going on

  6. #6
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Exclude specific text in cell string from highlighting

    sure, we love to work from examples.. it is hard to guess all outcomes by just changing one line of code.
    so after one or two guesses it is a good time to post the examples..

  7. #7
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: Exclude specific text in cell string from highlighting

    4 samples attached, OP, your 2 suggestions and required (which has module removed and background fill to represent)
    Attached Files Attached Files

  8. #8
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482

    Re: Exclude specific text in cell string from highlighting

    Like always once you see the whole code and example lines it makes more sence..
    it just needs another if test. instead of and / or condition

    Try this code

    Please Login or Register  to view this content.

  9. #9
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Exclude specific text in cell string from highlighting

    Quote Originally Posted by Roel Jongman View Post
    Like always once you see the whole code and example lines it makes more sence..
    it just needs another if test. instead of and / or condition

    Try this code

    Please Login or Register  to view this content.
    I am pretty sure you can replace what I highlighted in red above with this...
    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    11-12-2017
    Location
    Stafford, England
    MS-Off Ver
    365
    Posts
    184

    Re: Exclude specific text in cell string from highlighting

    Quote Originally Posted by Roel Jongman View Post
    Like always once you see the whole code and example lines it makes more sence..
    it just needs another if test. instead of and / or condition

    Try this code

    Please Login or Register  to view this content.
    Roel thanks very much, works just as I need it to.

+ 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] Need help with a formula that adds a string of values and will exclude specific text
    By JGREEN17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2019, 08:40 PM
  2. Replies: 24
    Last Post: 11-10-2015, 07:27 PM
  3. [SOLVED] Set a text value of a cell if a series of cells contains any specific text string
    By tubasparky in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-18-2014, 09:45 AM
  4. Conditional format: mark cells with 1 text string & exclude cells with another string
    By KArnoldColumbia in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2013, 01:43 AM
  5. [SOLVED] How to Exclude text in string in a single cell
    By leopoldc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-28-2013, 07:01 AM
  6. [SOLVED] To search specific text in a cell that contains text string
    By mikail in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-15-2013, 03:02 PM
  7. Replies: 2
    Last Post: 06-13-2013, 12:16 PM

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