+ Reply to Thread
Results 1 to 17 of 17

Highlight specific text in a cell all at once

  1. #1
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

    Highlight specific text in a cell all at once

    So I'm trying to highlight specific words/characters in a cell and I can do that, but I have to do it individually which is slow. Anyone know how if there is a way to specify multiple ranges to be highlighted all at once per cell?

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Highlight specific text in a cell all at once

    Hi Metaldrgn - You need to give us an example of what you have in the cell and what you are trying to highlight. Also explain how the text to highlight is determined, i.e. what criteria do you use to select the items to highlight when you do it manually? You can upload your file by clicking Go Advanced button as described in the amber notice at the top of this page.
    Last edited by JLGWhiz; 05-26-2020 at 03:45 PM.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    20

    Re: Highlight specific text in a cell all at once

    I attached the sheet I am working with. I am trying to move the highlighted value to a new column. Normally this would be just a basic SEARCH and REPLACE procedure. As I mentioned this is straightforward using the PC. So if I was working on the PC, I would not have this problem because all I will do is use SEARCH and REPLACE and job done. ON a PC, I use Search and Replace to SELECT ALL the values and Copy/paste them in the New Column that I want. Unfortunately this is not so easy on the Macintosh because the OPTIONS button that gives you EXTRA room for manoeuvre in the SEARCH dialogue box on a PC is missing on the Mac.

    Does this help?
    Last edited by Energy48; 05-26-2020 at 03:57 PM.

  4. #4
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    20

    Re: Highlight specific text in a cell all at once

    Quote Originally Posted by Metaldrgn View Post
    So I'm trying to highlight specific words/characters in a cell and I can do that, but I have to do it individually which is slow. Anyone know how if there is a way to specify multiple ranges to be highlighted all at once per cell?
    That is easily done with CONDITIONAL FORMATTING. You pick the Value and assign it a Colour and that is it.

    The problem I am having is how to SELECT ALL of these values and move them to a new column. On a PC it is easy using the SEARCH and REPLACE dialogue box. Not so on the Macintosh.
    Last edited by Energy48; 05-26-2020 at 04:02 PM.

  5. #5
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Highlight specific text in a cell all at once

    Quote Originally Posted by Energy48 View Post
    I attached the sheet I am working with. I am trying to move the highlighted value to a new column. Normally this would be just a basic SEARCH and REPLACE procedure. As I mentioned this is straightforward using the PC. So if I was working on the PC, I would not have this problem because all I will do is use SEARCH and REPLACE and job done. ON a PC, I will COPY ALL the selected values and paste them in the New Column that I want. Unfortunately this is not so easy on the Macintosh because the OPTIONS button that gives you EXTRA room for manoeuvre in the SEARCH dialogue box on a PC is missing on the Mac.

    Does this help?
    @Energy48 - Are you sure you posted to the correct thread? I think you want to get on this one
    https://www.excelforum.com/excel-pro...ml#post5338600
    Last edited by JLGWhiz; 05-26-2020 at 04:01 PM.

  6. #6
    Registered User
    Join Date
    04-30-2011
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    20

    Re: Highlight specific text in a cell all at once

    Quote Originally Posted by JLGWhiz View Post
    @Energy48 - Are you sure you posted to the correct thread? I think you want to get on this one
    https://www.excelforum.com/excel-pro...ml#post5338600
    Thanks for the Heads Up. How do I delete these comments from under this thread? I can't find the DELETE COMMENT button.
    .

  7. #7
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

    Re: Highlight specific text in a cell all at once

    Quote Originally Posted by JLGWhiz View Post
    Hi Metaldrgn - You need to give us an example of what you have in the cell and what you are trying to highlight. Also explain how the text to highlight is determined, i.e. what criteria do you use to select the items to highlight when you do it manually? You can upload your file by clicking Go Advanced button as described in the amber notice at the top of this page.
    I don't have the code here. Basically if a cell had text "have a good day" and I wanted to highlight have and good, I can do it one by one, but what I'm looking for is essentially a way to highlight all of it at once. Like I would make my passes with Instr to find the values and store them somewhere then after I've found all the criteria I want to go back through and highlight all of it. I can highlight specific text inside a cell using the characters method, but it's not allowing for anything more than one range.

    Or this would be feasible to do it one by one if I could do it in VBA and return it to excel after it was done. I'm trying to make it faster and yes I know the other tricks like screenupdating or whatever. I don't like using those and there are ways to vastly improve performance just in VBA without using that.
    Last edited by Metaldrgn; 05-26-2020 at 04:13 PM.

  8. #8
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

    Re: Highlight specific text in a cell all at once

    Quote Originally Posted by Energy48 View Post
    That is easily done with CONDITIONAL FORMATTING. You pick the Value and assign it a Colour and that is it.

    The problem I am having is how to SELECT ALL of these values and move them to a new column. On a PC it is easy using the SEARCH and REPLACE dialogue box. Not so on the Macintosh.
    That doesn't work because conditional formatting doesn't go beyond the cell.

  9. #9
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Highlight specific text in a cell all at once

    Quote Originally Posted by Metaldrgn View Post
    I don't have the code here. Basically if a cell had text "have a good day" and I wanted to highlight have and good, I can do it one by one, but what I'm looking for is essentially a way to highlight all of it at once. Like I would make my passes with Instr to find the values and store them somewhere then after I've found all the criteria I want to go back through and highlight all of it. I can highlight specific text inside a cell using the characters method, but it's not allowing for anything more than one range.

    Or this would be feasible to do it one by one if I could do it in VBA and return it to excel after it was done. I'm trying to make it faster and yes I know the other tricks like screenupdating or whatever. I don't like using those and there are ways to vastly improve performance just in VBA without using that.
    From what you are describing, I think you woud have to use the 'Split' function to create an array of the cell's content then bounce that against an array of the words you want to highlight and just highlight the matches between the two arrays. I dont believe there is a way to highlight them simultaneously, but they would be highligted in one execution of a double loop.

  10. #10
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Highlight specific text in a cell all at once

    I played around with it and came up with this:

    Please Login or Register  to view this content.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight specific text in a cell all at once

    Sample.

    Select the range first.
    Please Login or Register  to view this content.

  12. #12
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

    Re: Highlight specific text in a cell all at once

    From what I can tell those examples still replace one word at a time. I'm trying to find out if it's possible to change it all in the cell in one shot.

    jindon your code seems broken.
    Last edited by Metaldrgn; 05-27-2020 at 01:47 AM.

  13. #13
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight specific text in a cell all at once

    If you are not on Windows, it will not wok.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight specific text in a cell all at once

    Please Login or Register  to view this content.

  15. #15
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: Highlight specific text in a cell all at once

    Quote Originally Posted by Metaldrgn View Post
    From what I can tell those examples still replace one word at a time. I'm trying to find out if it's possible to change it all in the cell in one shot.

    jindon your code seems broken.
    the answer is No. They have to be addressed one at a time because there is no way (that I know of) to treat the various text as an entity for font change.
    Last edited by JLGWhiz; 05-27-2020 at 08:36 PM.

  16. #16
    Registered User
    Join Date
    01-15-2020
    Location
    somewhere
    MS-Off Ver
    2016
    Posts
    30

    Re: Highlight specific text in a cell all at once

    Quote Originally Posted by jindon View Post
    If you are not on Windows, it will not wok.
    I am using windows and it does not work. Looks like it's missing a quotation mark. I don't use reg ex much. When I build this, I will probably put it in a class. and use some kind of loop of instr to find all the occurrences for each word searched. Sucks there isn't a way to pass multiple criteria to characters or some other method to do this. Oh well should still suffice.

    Thanks for all the replies.
    Last edited by Metaldrgn; 05-27-2020 at 03:06 PM.

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Highlight specific text in a cell all at once

    Fixed one line.
    Please Login or Register  to view this content.

+ 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] Lookup specific text in a cell and highlight
    By ~TaC~ in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-04-2019, 03:20 AM
  2. [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
  3. [SOLVED] Highlight specific cells if cell contains text or numbers
    By besis in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-18-2018, 12:00 PM
  4. Using VBA to highlight specific text in cell
    By nav505 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-11-2013, 03:28 AM
  5. Replies: 3
    Last Post: 08-02-2013, 12:43 PM
  6. Specific Text in Range then highlight cell
    By gingank in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-11-2013, 10:41 AM
  7. [SOLVED] Highlight a range of cells if a single cell contains specific text
    By ZHertz in forum Excel General
    Replies: 3
    Last Post: 10-19-2012, 02:54 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