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]

Starting with the code from post #28, I would modify the red line and add the blue line

  Set Changed = Intersect(Target, Columns("B:P"))
  If Changed Is Nothing Then
    Set Changed = Intersect(Target, Union(Range("W9:Z9"), Range("W11:Z11")))
    If Not Changed Is Nothing Then Set Changed = Range("B9:P60")
  End If
  If Not Changed Is Nothing Then
  .
  .
  End If
  If Not Changed Is Nothing Then
    For i = 1 To 4
      Phrases(i) = Split(Range("W11:Z11").Cells(i).Value, "|")
    Next i
    Application.ScreenUpdating = False
    Changed.Font.Bold = False
    For Each c In Changed

 

[Mlogs]

Hey Peter, I have been working with these updates and they work flawlessly. Thank you again for helping me with this, you've been awesome!

 

[Peter_SSs]

Hey Peter, I have been working with these updates and they work flawlessly. Thank you again for helping me with this, you've been awesome!

Great news! Thanks for letting us know.

 

[souzoumos]

Hi,

Thank you for your code. I have used it several times.It works like a charm. This time though I am trying to colour column E as well when it finds a match in column A. I am really struggling to do so. Is it possible to help me with this? Thank you!!

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

 

[Peter_SSs]

This time though I am trying to colour column E as well when it finds a match in column A.

Welcome to the MrExcel board!

Since we are not familiar with your exact circumstances & requirements, could you post a small set of dummy data to show what sorts of things you have in columns A and E and also show or describe which ones should be highlighted and why? See Attachments for good ways to provide sample data.

 

[souzoumos]

Hi Thank you for your help.

Column A: Test Data				Column E: Test
R001 R004				R001
R005				R002
R000				R004
R000 R0003				R005
R005				R008
R006 R007				R006

<colgroup><col><col span="4"></colgroup><tbody>
</tbody>

The code that you provided previously checks the text in column E and if it match with a text in column A( even if it has several Rxxx codes) it colours it to red.That is working really well. But I would also like when it finds a match from the Rxxx codes in column E to colour them red as well.

For example: R001 should be coloured red in column E cell 1 and column A cell 1.
R005 should be coloured red in column E cell 4 and column A cell 2 and cell 5.
R006 should be coloured red in column E cell 7 and column A cell 7 but not the code R007 which is in cell 7 as well.
R000 and R008 should be NOT coloured at all as there is no match.

Thank you for your help.

Welcome to the MrExcel board!

Since we are not familiar with your exact circumstances & requirements, could you post a small set of dummy data to show what sorts of things you have in columns A and E and also show or describe which ones should be highlighted and why? See Attachments for good ways to provide sample data.

 

[Peter_SSs]

It seems that the values in column E are all just single values in each cell. In that case, try just adding the blue code into the previous code where shown.

    Next c
    With Range("E1", Range("E" & Rows.Count).End(xlUp))
      .FormatConditions.Delete
      .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(E1<>"""",MATCH(""*""&E1&""*"",A:A,0))"
      .FormatConditions(1).Font.Color = vbRed
    End With
    Application.ScreenUpdating = True
  End If
End Sub

 

[souzoumos]

Thank you! It works pefrectly

 

[Peter_SSs]

Thank you! It works pefrectly

You are welcome. Thanks for letting us know.

 

[Busto]

Does anyone know if this has been changed in the newer versions of Excel? I don't mind using VBA, but will be using a program to share the spreadsheet that doesn't allow macros. I am not hopeful that they have made the change in 2010, 2013, or 2016, but I thought ask just in case someone knew.

Thanks,
Busto