Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Excel Macro To Color Certain Words In A Line Of Text

  1. #1
    New Member
    Join Date
    Oct 2014
    Location
    London UK
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel Macro To Color Certain Words In A Line Of Text

    Hello,
    I currently have a list of banned words which I have named "content_check" and are in a tab called "Guide". I would like to make a macro which will check cells G13, G15, G19, G21 and E30:E6000 (if not blank) in the tab "A+_Creation" and highlight in red any words which match those in the banned words list. These cells can contain up to 1000 characters each and could contain more than 1 banned word.I'm currently using conditional formatting which turns any cells which contain a banned word red, but given that the list has almost 400 words it would be much better if only the word turned red instead of the whole cell.

    I noticed that something similar has already been covered in this post: http://www.mrexcel.com/forum/excel-q...s-excel-2.html
    and I tried editing Rick Rothstein's code to match my requirements, but it just crashes when I try to run it. I have good knowledge of formulas, but I'm still quite new to macros and something like this is beyond my technical ability. Would anyone know how to go about this?
    Any help would be hugely appreciated!

  2. #2
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,322
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Try This:-
    Code:
    Sub MG12Oct39
    Dim Rng             As Range
    Dim Dn              As Range
    Dim n               As Long
    Dim Dic             As Object
    Dim Sp              As Variant
    Dim c               As Long
    Set Rng = ActiveWorkbook.Names("content_checked").RefersToRange
        Set Dic = CreateObject("scripting.dictionary")
            Dic.CompareMode = vbTextCompare
                For Each Dn In Rng: Dic(Dn.Value) = Empty: Next
                 With Sheets("A+_Creation")
                    Set Rng = .Range("G15, G19, G21, E30:E6000")
                End With
    For Each Dn In Rng
        c = 0
        Sp = Split(Dn.Value, " ")
            For n = 1 To UBound(Sp)
                c = c + Len(Sp(n)) + 1
                If Dic.exists(Sp(n)) Then
                    Dn.Characters(c, Len(Sp(n))).Font.Color = vbRed
                End If
            Next n
    Next Dn
    End Sub
    Regards Mick

  3. #3
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Here is my code that you referenced in your question modified for what I think you described as your setup...
    Code:
    Sub ColorCertainWords()
      Dim Z As Long, Position As Long, Words As Variant, Cell As Range
      Words = Range("content_check")
      For Each Cell In Sheets("A+_Creation").Range("G13,G15,G19,G21,E30:E6000")
        If Len(Cell.Value) Then
            For Z = 1 To UBound(Words)
              Position = InStr(1, Cell.Value, Words(Z, 1), vbTextCompare)
              Do While Position
                Cell.Characters(Position, Len(Words(Z, 1))).Font.ColorIndex = 3  'Red
                Position = InStr(Position + 1, Cell.Value, Words(Z, 1), vbTextCompare)
              Loop
            Next
        End If
      Next
    End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  4. #4
    New Member
    Join Date
    Oct 2014
    Location
    London UK
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Quote Originally Posted by Rick Rothstein View Post
    Here is my code that you referenced in your question modified for what I think you described as your setup...
    Code:
    Sub ColorCertainWords()
      Dim Z As Long, Position As Long, Words As Variant, Cell As Range
      Words = Range("content_check")
      For Each Cell In Sheets("A+_Creation").Range("G13,G15,G19,G21,E30:E6000")
        If Len(Cell.Value) Then
            For Z = 1 To UBound(Words)
              Position = InStr(1, Cell.Value, Words(Z, 1), vbTextCompare)
              Do While Position
                Cell.Characters(Position, Len(Words(Z, 1))).Font.ColorIndex = 3  'Red
                Position = InStr(Position + 1, Cell.Value, Words(Z, 1), vbTextCompare)
              Loop
            Next
        End If
      Next
    End Sub
    This is exactly what I was after and it works brilliantly! Thank you so much!! You just saved a lot of people a ton of time!

  5. #5
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,322
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Code ERROR
    Please change this:-
    Code:
    Set rng = ActiveWorkbook.Names("content_checked").RefersToRange
    For This:-
    Code:
    Set rng = ActiveWorkbook.Names("content_check").RefersToRange

  6. #6
    New Member
    Join Date
    Oct 2014
    Location
    London UK
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Hi MickG,

    Thank you for your response. I tried your code as well, but it hasn't worked for me. I assigned the macro to a button, but nothing appears to happen when I click on it as the banned words remain black in the cell.

  7. #7
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,322
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Not to worry, You perhaps have'nt changed the line as per the Reply above !!!!!

  8. #8
    New Member
    Join Date
    Sep 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Hello Rick,

    This worked really well for me as well. However how can it be modified to highlight only whole words.

    My word list contains names such as Gandhi, To, Ha, Smith, La.

    It does find the names in the below, but also subsets of names are highlighted (see below). How can the code be chandes to only find whole words- or words with spaces before or after them?

    Gandhi, Jason, Kaplan, Shaffy, Noel L. Smith, and Sardar Ali Khan. “The Molecular Biology of Prostate Cancer: Current Understanding and Clinical Implications.” Prostate Cancer and Prostatic Diseases, December 27, 2017.

    I hope that this makes sense? I am very new to VBA.

    Cheers
    Ian

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,909
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Quote Originally Posted by imurra View Post
    Hello Rick,

    This worked really well for me as well. However how can it be modified to highlight only whole words.

    My word list contains names such as Gandhi, To, Ha, Smith, La.

    It does find the names in the below, but also subsets of names are highlighted (see below). How can the code be chandes to only find whole words- or words with spaces before or after them?
    See if this version of my code does what you want...
    Code:
    Sub ColorCertainWords() Dim Z As Long, Position As Long, Words As Variant, Cell As Range Words = Range("content_check") For Each Cell In Sheets("A+_Creation").Range("G15,G13,G19,G21,E30:E6000") If Len(Cell.Value) Then For Z = 1 To UBound(Words) Position = InStr(1, Cell.Value, Words(Z, 1), vbTextCompare) Do While Position If "_" & Mid(Cell.Value, Position) & "_" Like "[!A-Za-z0-9]" & Words(Z, 1) & "[!A-Za-z0-9]*" Then Cell.Characters(Position, Len(Words(Z, 1))).Font.ColorIndex = 3 'Red End If Position = InStr(Position + 1, Cell.Value, Words(Z, 1), vbTextCompare) Loop Next End If Next End Sub
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    Sep 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel Macro To Color Certain Words In A Line Of Text

    Quote Originally Posted by Rick Rothstein View Post
    See if this version of my code does what you want...
    Code:
    Sub ColorCertainWords() Dim Z As Long, Position As Long, Words As Variant, Cell As Range Words = Range("content_check") For Each Cell In Sheets("A+_Creation").Range("G15,G13,G19,G21,E30:E6000") If Len(Cell.Value) Then For Z = 1 To UBound(Words) Position = InStr(1, Cell.Value, Words(Z, 1), vbTextCompare) Do While Position If "_" & Mid(Cell.Value, Position) & "_" Like "[!A-Za-z0-9]" & Words(Z, 1) & "[!A-Za-z0-9]*" Then Cell.Characters(Position, Len(Words(Z, 1))).Font.ColorIndex = 3 'Red End If Position = InStr(Position + 1, Cell.Value, Words(Z, 1), vbTextCompare) Loop Next End If Next End Sub
    It worked wonderfully. I modified it a bit and put in explanations for new users like myself. All credit to Rick Rodstein.
    Sub ColorCertainWords()
    Dim Z As Long, Position As Long, Words As Variant, Cell As Range
    ' insert range of list of words to use to search for
    Words = Range("d2:d237")
    ' insert range in which to find words
    For Each Cell In Sheets("Sheet1").Range("a2:a318")
    If Len(Cell.Value) Then
    For Z = 1 To UBound(Words)
    Position = InStr(1, Cell.Value, Words(Z, 1), vbTextCompare)
    Do While Position
    If "_" & Mid(Cell.Value, Position) & "_" Like "[!A-Za-z0-9]" & Words(Z, 1) & "[!A-Za-z0-9]*" Then
    Cell.Characters(Position, Len(Words(Z, 1))).Font.ColorIndex = 3 'Red
    End If
    Position = InStr(Position + 1, Cell.Value, Words(Z, 1), vbTextCompare)
    Loop
    Next
    End If
    Next
    End Sub

Some videos you may like

User Tag List

Tags for this Thread

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
  •