+ Reply to Thread
Results 1 to 21 of 21

Highlight row based on text within string in cell

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

    Highlight row based on text within string in cell

    Hi,

    I have a spreadsheet where text in column I contains comments. In these comments I have reference numbers which are sometimes entered incorrectly.

    Comments may include:
    TCN95384 A4891644922
    A4884103393 ABANDONED IN STORE
    ABANDONED AT DC A4864666884
    A4655177099 no carrier number

    The reference starting with 'A' always has 10 numbers following it as can be seen.

    Is there a way to highlight the row if the reference has more/less than 10 numbers to highlight the error?

    Duplicate post
    https://www.excelforum.com/excel-for...highlight.html
    as I was looking to solve this with formula so I can use conditional formatting, if anyone can do this then I'd be interested to see how it works in my situation

    Thanks
    Last edited by ~TaC~; 08-18-2019 at 12:21 PM.

  2. #2
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Highlight row based on text within string in cell

    Try this:-
    Please Login or Register  to view this content.
    Regards Mick

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

    Re: Highlight row based on text within string in cell

    Thanks, that seems to work great.

    I changed
    Dn.Interior.Color = vbYellow to
    Dn.EntireRow.Interior.ColorIndex = 3 to highlight entire row.

    I also changed "A" & <>10 to "TCN" & <>5 to get the same method working with the TCN reference but it didn't work.

    Please Login or Register  to view this content.
    Does something else need changing?
    Last edited by ~TaC~; 08-16-2019 at 05:58 PM.

  4. #4
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Highlight row based on text within string in cell

    Try this:-
    Please Login or Register  to view this content.
    Regards Mick

  5. #5
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,229

    Re: Highlight row based on text within string in cell

    Here is another macro that you can try (change what's highlighted in red to actual data range's column letter and starting row number)...
    Please Login or Register  to view this content.
    For your second question, try this variation...
    Please Login or Register  to view this content.

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,608

    Re: Highlight row based on text within string in cell

    Hi Rick,

    I've not seen you posting on here for ages - welcome back

    Pete

  7. #7
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,229

    Re: Highlight row based on text within string in cell

    Quote Originally Posted by Pete_UK View Post
    Hi Rick,
    I've not seen you posting on here for ages - welcome back
    Thanks Pete! I am surprised that someone remembers me as I do not have a lot of posting in this forum. The MrExcel forum is my main place, but an online search for something I was looking up brought me back to this forum, so in my spare time, I have started revisiting here.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,608

    Re: Highlight row based on text within string in cell

    I remember you most from the Code Cage and the old Newsgroups - some of your one-liners were amazing.

    Pete

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

    Re: Highlight row based on text within string in cell

    Quote Originally Posted by Pete_UK View Post
    I remember you most from the Code Cage and the old Newsgroups - some of your one-liners were amazing.
    Ah, the old newsgroups... I still miss them. I still post one-liners and minimalistic codes over in the MrExcel forum... and I'll post them here if the question lends itself to that kind of solution.

  10. #10
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight row based on text within string in cell

    When I said that it would probably need vba that was not an invitation to break forum rules and start another thread.

    Duplicate of https://www.excelforum.com/excel-for...highlight.html

    Reported to moderators for decision.

    As per rule 6, this thread should receive no further replies until this issue has been addressed by a moderator.

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: Highlight row based on text within string in cell

    To confirm what jason said:

    Edited mistakenly said X-posting.

    Should be duplicate thread.

    Thank you jason for correcting me. Post #12 below.
    Last edited by FlameRetired; 08-16-2019 at 03:07 PM.
    Dave

  12. #12
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Highlight row based on text within string in cell

    Dave, just to confirm, I was flagging this as a duplicate of another thread here, not a cross post.

    If it has been cross posted then that makes 3 rules broken in this thread (missing code tags in post #2).

    No doubt the OP will say that they were not aware of the rules Ignorantia juris non excusat

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: Highlight row based on text within string in cell

    Pease see your post #3.

    Administrative Note:

    We would very much like to help you with your query, however you need to include code tags around your code.

    Please take a moment to add the tags. Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

    Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,673

    Re: Highlight row based on text within string in cell

    jason

    Thank you. Will follow up.

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

    Re: Highlight row based on text within string in cell

    Thanks Mick & Rick, suggestions work great!

  16. #16
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Highlight row based on text within string in cell

    You're welcome

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

    Re: Highlight row based on text within string in cell

    Hi,

    Sorry to drop back on this one but my project keeps evolving.

    How would I adapt the following code thanks to MickG to only highlight columns A:L in a row rather than .EntireRow

    Please Login or Register  to view this content.
    Regards
    Tez

  18. #18
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,630

    Re: Highlight row based on text within string in cell

    You might want to remove the SOLVED tag ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

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

    Re: Highlight row based on text within string in cell

    Haha yeah thats a fair point... thanks

  20. #20
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    Re: Highlight row based on text within string in cell

    Try this:-
    Please Login or Register  to view this content.
    Regards Mick
    Last edited by MickG; 08-19-2019 at 07:54 AM.

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

    Re: Highlight row based on text within string in cell

    I am so sorry, I now realise I never stated what column I am referencing for the lookup.

    Column L is where the data is and starts row 6, in your previous code changing A2 to L6 had the same result in EntireRow colour change.
    Now with your new code I change to L6 and it changes L onward.

    However I have learnt something though so not an entire waste of time. I see that (, 12) changes the number of columns affected.

    So, reference numbers are in column L and I would like to colour change A:L

    Again sorry for wasting your time.

    Tez

+ 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] How to highlight a specific cell based on value/text/number
    By Byambadorj in forum Excel General
    Replies: 13
    Last Post: 07-18-2018, 03:41 AM
  2. How to highlight the cell based on text
    By Ankit_Kumar in forum Excel General
    Replies: 4
    Last Post: 06-11-2015, 02:25 AM
  3. Conditional Formatting to highlight cell based on values in a string
    By thrst2no in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-17-2015, 09:23 AM
  4. Replies: 6
    Last Post: 10-23-2014, 04:48 PM
  5. Attempting to Highlight Cells Based on String within Cell
    By rmdlp in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-23-2014, 04:30 PM
  6. [SOLVED] Highlight row based on character/text string containing in cell.
    By dihris in forum Excel General
    Replies: 2
    Last Post: 12-03-2012, 11:16 AM
  7. [SOLVED] Highlight a portion of string text (the string text is a value of a formula)
    By RaymondH in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-02-2012, 06:50 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