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.

 

[gcarv]

Thanks Peter that worked like a charm Thank you SOOO MUCH that was a big help!

 

[Peter_SSs]

Thanks Peter that worked like a charm Thank you SOOO MUCH that was a big help!

Cheers. Thanks for letting us know.

 

[grifaz]

Hannah

Tom doesn't seem to be about as much as he used to be, so I'll try a modification for you to test.
Note that Tom's code is case-sensitive.
It still uses cell B1 to get the string to be identified in the column G cells.
Test on a set of data that is not too big to start with,

Sub Test1()
  Dim strString$, x&
  Dim rngCell As Range
 
  strString = Range("B1").Value
  Application.ScreenUpdating = False
  For Each rngCell In Range("G1", Range("G" & Rows.Count).End(xlUp))
    With rngCell
      .Font.ColorIndex = 1
      For x = 1 To Len(.Text) - Len(strString) Step 1
        If Mid(.Text, x, Len(strString)) = strString Then .Characters(x, Len(strString)).Font.ColorIndex = 5
      Next x
    End With
  Next rngCell
  Application.ScreenUpdating = True
End Sub

I just registered and can confirm this still works on Office 365. And just like Hannah, I have no experience with programming and VBA, so the solution by @Tom Urtis and @Peter_SSs just came as a godsend during my project work on a pattern that repeats 622 times. Thank you so much!