Conditional Formatting: Highlight only certain words in cell (text)? - Page 6

Thanks Thanks:  0
Page 6 of 6 FirstFirst ... 456
Results 51 to 60 of 60

Thread: Conditional Formatting: Highlight only certain words in cell (text)?

  1. #51
    New Member
    Join Date
    Nov 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

     
    Not necessary "daddy's" only "daddy".

  2. #52
    New Member
    Join Date
    Nov 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Quote Originally Posted by Peter_SSs View Post
    With "daddy" in column B, which of these, if any, would it do?
    My daddy's coat is blue.
    My daddy's coat is blue.
    My daddy's coat is blue.
    First one would do it just fine: My daddy's coat is blue.

    Thank you!

  3. #53
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Quote Originally Posted by Tulpa View Post
    First one would do it just fine: My daddy's coat is blue.

    Thank you!
    Try this slight modification of the post #17 code on a copy of your workbook.

    Code:
    Sub Highliht_Words()
      Dim RX As Object, Mtchs As Object
      Dim itm As Variant
      Dim c As Range
      
      Set RX = CreateObject("VBScript.RegExp")
      RX.Global = True
      RX.IgnoreCase = True
      RX.Pattern = "\|{2,}"
      RX.Pattern = "\b(" & RX.Replace(Join(Application.Transpose(Range("B1", Range("B" & Rows.Count).End(xlUp)).Value), "|"), "|") & ")\b"
      Application.ScreenUpdating = False
      Columns("A").Font.ColorIndex = xlAutomatic
      For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Set Mtchs = RX.Execute(c.Value)
        For Each itm In Mtchs
          c.Characters(Start:=itm.firstindex + 1, Length:=itm.Length).Font.Color = vbRed
        Next itm
      Next c
      Application.ScreenUpdating = True
    End Sub
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  4. #54
    New Member
    Join Date
    Nov 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Quote Originally Posted by Peter_SSs View Post
    Try this slight modification of the post #17 code on a copy of your workbook.

    Code:
    Sub Highliht_Words()
      Dim RX As Object, Mtchs As Object
      Dim itm As Variant
      Dim c As Range
      
      Set RX = CreateObject("VBScript.RegExp")
      RX.Global = True
      RX.IgnoreCase = True
      RX.Pattern = "\|{2,}"
      RX.Pattern = "\b(" & RX.Replace(Join(Application.Transpose(Range("B1", Range("B" & Rows.Count).End(xlUp)).Value), "|"), "|") & ")\b"
      Application.ScreenUpdating = False
      Columns("A").Font.ColorIndex = xlAutomatic
      For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Set Mtchs = RX.Execute(c.Value)
        For Each itm In Mtchs
          c.Characters(Start:=itm.firstindex + 1, Length:=itm.Length).Font.Color = vbRed
        Next itm
      Next c
      Application.ScreenUpdating = True
    End Sub
    Beautiful, works like a charm! This made my work so much easier, thank you very much Peter!
    Is there any way to support/contribute your work, this forum?

  5. #55
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Quote Originally Posted by Tulpa View Post
    Beautiful, works like a charm! This made my work so much easier, thank you very much Peter!
    Is there any way to support/contribute your work, this forum?
    This is a free forum. The only support that we want (or even allow) is your thanks, so you have done all that is needed.
    Glad it worked out for you.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  6. #56
    New Member
    Join Date
    Nov 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Hi Peter

    I was wondering if you could assist me in relation to the variety of codes here.

    I would like to bold a single word in a string that is a returned value from the formula within various cells in a sheet.

    Can any of the above codes be amended this way?

    the word is the same 'headspace'.

    Kind regards

    Scott

  7. #57
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    35,599
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Welcome to the MrExcel board!

    Quote Originally Posted by UnitedCloud01 View Post
    I would like to bold a single word in a string that is a returned value from the formula ..
    Unfortunately, it is not possible to bold part of a text that is the result of a formula.

    If the formula is replaced by its result, then the text can be formatted that way. In that case though, the cell would no longer contain a formula so would not dynamically change if the original input cell(s) change.
    Hope this helps, good luck.
    Peter
    Excel 365 + Excel 2010, 2007 - Windows 10, 7
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker
    - Read: Forum Rules & Forum Use Guidelines

  8. #58
    New Member
    Join Date
    Nov 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Hi Peter

    Thanks for the reply. I appreciate it.

    Regards

    Scott

  9. #59
    New Member
    Join Date
    Nov 2017
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

    Actually, Peter, I was wondering if it was possible to amend this VBA that allows me to count colours created by conditional formatting:

    Code:
    Function WrapCountClosures(rRange)
        WrapCountClosures = rRange.Parent.Evaluate("CountClosures(" & _
                              rRange.Address(False, False) & ")")
    End Function
    'can't call this directly from a worksheet but can be called via evaluate
    Public Function CountClosures(rRange As Range)
        Dim rCell As Range
        Dim vResult
        For Each rCell In rRange
          If rCell.DisplayFormat.Interior.ColorIndex = 42 Then
                 vResult = 1 + vResult
          End If
        Next rCell
        CountClosures = vResult
    End Function
    In sheet code:

    WrapCountClosures(F5:F12)

  10. #60
    New Member
    Join Date
    Oct 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Conditional Formatting: Highlight only certain words in cell (text)?

      
    Hello,

    I tried to insert some of the VBA from early on in the post & modify to suit my needs, but it made all text change color. I'm not familiar with VBA and obviously I did something wrong.


    Here's what I have: a few hundred rows of data. Column O contains text comments on some but not all rows. What I would like is for every instance of the words "drop" or "drops" in Column O for those words only to appear red. Is that possible?

    Thanks

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com