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.

 

[Tulpa]

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

 

[Tulpa]

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!

 

[Peter_SSs]

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.

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

 

[Tulpa]

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

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?

 

[Peter_SSs]

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.

 

[UnitedCloud01]

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

 

[Peter_SSs]

Welcome to the MrExcel board!

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.

 

[UnitedCloud01]

Hi Peter

Thanks for the reply. I appreciate it.

Regards

Scott

 

[UnitedCloud01]

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

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)

 

[d2ward]

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