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

[Attraktor]

Hi to all,

I know that you can highlight a cell or it's entire contents with a certain color, but can you highlight only certain portions of it's contents using Conditional Formatting?

A simple example: If cell A1 contains the text "The pink elephant has blue and green spots." and cell B1 contains the text "blue", using cell B1 as a reference, is there a way to highlight only the text "blue" in A1 with the color blue using Conditional Formatting?

Thanks in advance for any answer to this.


Marc.

 

[Peter_SSs]

Works PEFECTLY! Thank you

You are welcome.

 

[Tom Urtis]

Thanks from me too, Peter.

 

[Peter_SSs]

Thanks from me too, Peter.

Cheers Tom. Good to see you here.
Was I wrong about you being scarcer here lately or are we just on at different time zones?

@Hannah
I'm not sure how much data you have in column G so speed may not be an issue but here is an alternative that ..
- Allows you to do this in a case-sensitive way if you want (change the 'Const bCase ..' line)
- Is considerably faster.

Sub Format_Text()
  Dim rCell As Range
  Dim lPos As Long, lComp As Long, lLngth As Long
  Dim sCell As String
 
  Const sTxt As String = "Blue"   '<- Text to format
  Const bCase As Boolean = False  '<- True for Case-Sensitive matching
  
  Application.ScreenUpdating = False
  lComp = 1 + bCase
  lLngth = Len(sTxt)
  Columns("G").Font.ColorIndex = 1
  For Each rCell In Range("G1", Range("G" & Rows.Count).End(xlUp))
    With rCell
      sCell = .Text
      lPos = InStr(1, sCell, sTxt, lComp)
      Do Until lPos = 0
        .Characters(lPos, lLngth).Font.ColorIndex = 5
        lPos = InStr(lPos + lLngth, sCell, sTxt, lComp)
      Loop
    End With
  Next rCell
  Application.ScreenUpdating = True
End Sub

 

[Tom Urtis]

Cheers Tom. Good to see you here.
Was I wrong about you being scarcer here lately or are we just on at different time zones?

Yes, you're right, I've been scarcer here lately, while working on Excel projects for my business that I plan to share in the near future. Thanks again for helping out on this question.

 

[hannahbarton]

Cheers Tom. Good to see you here.
Was I wrong about you being scarcer here lately or are we just on at different time zones?

@Hannah
I'm not sure how much data you have in column G so speed may not be an issue but here is an alternative that ..
- Allows you to do this in a case-sensitive way if you want (change the 'Const bCase ..' line)
- Is considerably faster.

Sub Format_Text()
  Dim rCell As Range
  Dim lPos As Long, lComp As Long, lLngth As Long
  Dim sCell As String
 
  Const sTxt As String = "Blue"   '<- Text to format
  Const bCase As Boolean = False  '<- True for Case-Sensitive matching
  
  Application.ScreenUpdating = False
  lComp = 1 + bCase
  lLngth = Len(sTxt)
  Columns("G").Font.ColorIndex = 1
  For Each rCell In Range("G1", Range("G" & Rows.Count).End(xlUp))
    With rCell
      sCell = .Text
      lPos = InStr(1, sCell, sTxt, lComp)
      Do Until lPos = 0
        .Characters(lPos, lLngth).Font.ColorIndex = 5
        lPos = InStr(lPos + lLngth, sCell, sTxt, lComp)
      Loop
    End With
  Next rCell
  Application.ScreenUpdating = True
End Sub

Thanks, I'll give it a go! Only up to about 5000 rows of data so not too bad at all, speed wasn't a problem but I'll change it in case I need to rerun with more.

 

[BaptisteEx]

Dear all,

First, thank you for your VBA codes , it has already helped me a lot.

I'm trying to modify the last code posted in order to suit more my needs but without success...
Could someone help me with that ?
I would like the code to check and flag not only one string in the column A but actually a list of 80 different strings.
The best could be to link another column containing the 80 words directly in this code. Those words could change often.

Context : My goal is to allow users to flag quickly potential risky words in a big file containing label texts for products.

Thank you !!

BaptisteEx

 

[Peter_SSs]

I would like the code to check and flag not only one string in the column A but actually a list of 80 different strings.
The best could be to link another column containing the 80 words directly in this code. Those words could change often.

Context : My goal is to allow users to flag quickly potential risky words in a big file containing label texts for products.

Welcome to the MrExcel board!

You could try this in a copy of your workbook.
I have assumed that the text to be checked/coloured is in column A and the list of words to check for are in column E.

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by adding/deleting/altering values in column E.
4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled workbook (*.xlsm) & you will need to enable macros.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object, Mtchs As Object
  Dim itm As Variant
  Dim c As Range
  
  If Not Intersect(Target, Columns("E")) Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
    RX.Pattern = "\b(" & Join(Application.Transpose(Range("E1", Range("E" & 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 If
End Sub

 

[BaptisteEx]

Thank you !

I've copied several times this part of the code (below) and adapted it to extend the search from column A to J, (and I use column with words to check in M).
It's working but if I'm changing too much the column M, I can see some corruptions of text inside A to J after a while.
But the first time it's ok so it's more than enough for me.

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

 

[Peter_SSs]

I've copied several times this part of the code ..

To deal with multiple columns, you shouldn't need to repeat the code. For highlighting in columns A:J with list in column M, try this.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object, Mtchs As Object
  Dim itm As Variant
  Dim c As Range
  Dim lr As Long

  If Not Intersect(Target, Columns("M")) Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
    RX.Pattern = "\b(" & Join(Application.Transpose(Range("M1", Range("M" & Rows.Count).End(xlUp)).Value), "|") & ")\b"
    Application.ScreenUpdating = False
    Columns("A:J").Font.ColorIndex = xlAutomatic
    lr = Columns("A:J").Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, SearchFormat:=False).Row
    For Each c In Range("A1:J" & lr)
      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 If
End Sub

Note:
If it is possible that blank cells exist among the list in column M, then everything in the target columns would turn red. If that is possible with your circumstances then the code could be further modified to cope with that.

 

[Mlogs]

Hi,

I'm very interested in making this code work for me. I tried to use the code you just posted but whenever I click on run the select macro box opens up and nothing is in it, am I doing something wrong? If I manipulate anything in the code it has an error, but when the code is just the way you have it typed it acts like it doesn't see it.

I am also looking to expand on this code, but I have no idea how to do the things I would like to do. I have a report that I export into excel, it populates into columns B through P and only rows 9 through roughly 50. I would like to be able to have 4 colors for key words (yellow, red, blue, green) that would reside in columns W, X, Y and Z respectively.

If it isn't too much to ask, is it possible for you to add to the code to make this possible?

Thank You.