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]

Welcome to the MrExcel board!

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?

Yes you are. The code is designed to run automatically, not instigated by the user. That may not suit you but if you want to test the code as it is, read the installation/operating instructions given in post #17.

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?

Before suggesting specific code changes, I would like to know more about your actual data and layout. Can you post a small set of dummy sample data (columns B:E and columns W:Z and say 5-10 rows should do) and explain in relation to that sample data exactly what you want to happen? My signature block below has a link with suggestions for posting sample data.

 

[Mlogs]

Book1
	B	C	D	E	F
9	751280004 4//	-- Down Spout Piping, -- Side Sewer, -- Pipe Inspection		-- Back Fill Day 1	-- [QI] Backfill, -- Back Fill Day 2 Final, -- Final Utilities
10	751280010 10//	-- Pour Footing, -- Footing Inspection		-- Set Walls Day 1	-- Set Walls Day 2 - Final, -- [QI] Poured Foundation, -- Call In Wall Inspection
11	751280013 13//	-- RI Heat Day 2 - Final, -- Install Siding Day 2			-- RI Electric Day 1, -- [QI] Heating, -- Duct Blast, -- Install Siding Day 3
12	751280014 14//	-- Crawl Beams and Subfloor, -- Under Floor inspection, -- Prefab Stairs , -- Structural Deco Column, -- Rim Vents Cut		-- Frame walls	-- Deliver 2nd Floor Joists - Roof, -- (QI) Lumber check 2
13	751280017 17//
14	751280018 18//	-- [QI] Framing Review, -- [QI] Electrical, -- Penetrations, -- Install Siding Day 5, -- Call In Frame, Mechanical, & Plumb Inspection, -- Electrical Inspection, -- Final Frame Pick-Up, -- Notify Buyer of Completion of Rough In's		-- Frame, Mechanical, and Plumb Inspection Day 1, -- Install Siding Day 6 - Final	-- Frame, Mechanical, and Plumb Inspection Day 2 - Final
Weekly_Construction_Schedule

Book1
	W	X	Y	Z
9	"Down Spout Piping" "Rim Vents Cut"	"[QI] Heating" "[QI] Backfill"	"Call in Wall Inspection" "Call In Frame, Mechanical, & Plumb Inspection"	"Pour Footing"
Weekly_Construction_Schedule

The phrase's in columns W - Z are the key phrases. Any key phrase located in columns B - P that match would be changed to a different color. All the phrases in column W would change to Yellow, column X would change to Green, column Y would change to blue and column Z would change to red.

I added the quotations to indicate the start and stop of each key phrase, is there another way to do this? As only changing the entire phrase would be best for my situation.

I hope this is not asking too much but this would save me so much time each week.

Also having the code function automatically and not requiring me to tell it to run would be awesome.

Thank you for all your help in this endeavor.

 

[Peter_SSs]

I added the quotations to indicate the start and stop of each key phrase, is there another way to do this?

I would suggest using a single delimiter that will not appear elsewhere in columns W:Z. For example, I have used a "|" character here.

Excel Workbook
	W	X	Y	Z
9	Down Spout Piping|Rim Vents Cut	[QI] Heating|[QI] Backfill	Call in Wall Inspection|Call In Frame, Mechanical, & Plumb Inspection	Pour Footing
Sheet3

Because of some of the characters in your data, it doesn't lend itself readily to the use of "Regular Expressions" that I had used in the previous code so I have used another approach below.

I have assumed that your column B:P data resides within rows 9 to 60.
I have also assumed that for the column W:Z phrases, they only occupy a single row: row 9. If that is not the case you would need to explain more about how that should work.

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**.
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.

** Initially copy cell W9 and paste it back into that same cell. That should trigger the code to do an initial pass over all the data in columns B:P.
After that, if you change any cells in columns B:P, only those cells should get re-processed but if you change any of the W9:Z9 cells, then the whole B:P range will get re-processed.

Test in a copy of your workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Phrases(1 To 4) As Variant
  Dim Colrs(1 To 4) As Long
  Dim c As Range, Changed As Range
  Dim i As Long, j As Long, pos As Long, LenPhr As Long
  Dim Phr As String, cVal As String

  Set Changed = Intersect(Target, Columns("B:P"))
  If Changed Is Nothing Then
    Set Changed = Intersect(Target, Range("W9:Z9"))
    If Not Changed Is Nothing Then Set Changed = Range("B9:P60")
  End If
  If Not Changed Is Nothing Then
    For i = 1 To 4
      Phrases(i) = Split(Range("W9:Z9").Cells(i).Value, "|")
    Next i
    Colrs(1) = vbYellow: Colrs(2) = vbGreen: Colrs(3) = vbBlue: Colrs(4) = vbRed
    Application.ScreenUpdating = False
    Changed.Font.Color = vbBlack
    For Each c In Changed
      cVal = c.Value
      If Len(cVal) > 0 Then
        For i = 1 To 4
          For j = 0 To UBound(Phrases(i))
            Phr = Phrases(i)(j)
            LenPhr = Len(Phr)
            pos = 0
            Do
              pos = InStr(pos + 1, cVal, Phr)
              If pos > 0 Then c.Characters(Start:=pos, Length:=LenPhr).Font.Color = Colrs(i)
            Loop Until pos = 0
          Next j
        Next i
      End If
    Next c
    Application.ScreenUpdating = True
  End If
End Sub

 

[Mlogs]

Peter,

Thank you so much for your time to create this code and the amazingly quick response. I have only begun to work with it but it is working flawlessly. I can't thank you enough for the effort you put into this!

You sir, are amazing!

 

[Peter_SSs]

Peter,

Thank you so much for your time to create this code and the amazingly quick response. I have only begun to work with it but it is working flawlessly. I can't thank you enough for the effort you put into this!

You sir, are amazing!

You are very welcome. Thank you also for your kind comments.

 

[Mlogs]

Hi Peter,

The code you have written has been working perfectly. Thank you. However, some of the people I have been sending this report to don't have access to color printers. If its not too much trouble could you add to the code for cells W11:Z11 to function the exact same way as W9:Z9 with the exception that they bold the given key phrase instead of coloring the key phrase.

This would allow me to have either colored or bolded phrases, or any combination of the two.

Thank you.

 

[Peter_SSs]

I don't understand exactly what you have in mind about the choice of coloured or bolded. I also don't understand the bolded in that it would not distinguish between the phrases in columns W:Z like the different colours do.

Perhaps you could consider this, or pick what parts you want. The code does the following. For phrases in column ...

W, the text will go yellow and bold
X, the text will go green and italic
Y, the text will go blue and underlined
Z, the text will go red and double underlined

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Phrases(1 To 4) As Variant
  Dim Colrs(1 To 4) As Long
  Dim c As Range, Changed As Range
  Dim i As Long, j As Long, pos As Long, LenPhr As Long
  Dim Phr As String, cVal As String

  Set Changed = Intersect(Target, Columns("B:P"))
  If Changed Is Nothing Then
    Set Changed = Intersect(Target, Range("W9:Z9"))
    If Not Changed Is Nothing Then Set Changed = Range("B9:P60")
  End If
  If Not Changed Is Nothing Then
    For i = 1 To 4
      Phrases(i) = Split(Range("W9:Z9").Cells(i).Value, "|")
    Next i
    Colrs(1) = vbYellow: Colrs(2) = vbGreen: Colrs(3) = vbBlue: Colrs(4) = vbRed
    Application.ScreenUpdating = False
    With Changed.Font
      .Color = vbBlack
      .Bold = False
      .Italic = False
      .Underline = xlUnderlineStyleNone
    End With
    For Each c In Changed
      cVal = c.Value
      If Len(cVal) > 0 Then
        For i = 1 To 4
          For j = 0 To UBound(Phrases(i))
            Phr = Phrases(i)(j)
            LenPhr = Len(Phr)
            pos = 0
            Do
              pos = InStr(pos + 1, cVal, Phr)
              If pos > 0 Then
                With c.Characters(Start:=pos, Length:=LenPhr).Font
                  .Color = Colrs(i)
                  Select Case i
                    Case 1: .Bold = True
                    Case 2: .Italic = True
                    Case 3: .Underline = xlUnderlineStyleSingle
                    Case 4: .Underline = xlUnderlineStyleDouble
                  End Select
                End With
              End If
            Loop Until pos = 0
          Next j
        Next i
      End If
    Next c
    Application.ScreenUpdating = True
  End If
End Sub

 

[Mlogs]

Hi,

Thanks again for getting back so quick. After doing some messing around with the last code you wrote and the previous code, I have modified the previous code to work for me. Here is what I came up with.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Phrases(1 To 4) As Variant
  Dim Colrs(1 To 4) As Long
  Dim c As Range, Changed As Range
  Dim i As Long, j As Long, pos As Long, LenPhr As Long
  Dim Phr As String, cVal As String

  Set Changed = Intersect(Target, Columns("B:P"))
  If Changed Is Nothing Then
    Set Changed = Intersect(Target, Range("W9:Z9"))
    If Not Changed Is Nothing Then Set Changed = Range("B9:P60")
  End If
  If Not Changed Is Nothing Then
    For i = 1 To 4
      Phrases(i) = Split(Range("W9:Z9").Cells(i).Value, "|")
    Next i
    Colrs(1) = vbCyan: Colrs(2) = vbGreen: Colrs(3) = vbBlue: Colrs(4) = vbRed
    Application.ScreenUpdating = False
    Changed.Font.Color = vbBlack
    For Each c In Changed
      cVal = c.Value
      If Len(cVal) > 0 Then
        For i = 1 To 4
          For j = 0 To UBound(Phrases(i))
            Phr = Phrases(i)(j)
            LenPhr = Len(Phr)
            pos = 0
            Do
              pos = InStr(pos + 1, cVal, Phr)
              If pos > 0 Then c.Characters(Start:=pos, Length:=LenPhr).Font.Color = Colrs(i)
            Loop Until pos = 0
          Next j
        Next i
      End If
    Next c
    Application.ScreenUpdating = True
  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
    For Each c In Changed
      cVal = c.Value
      If Len(cVal) > 0 Then
        For i = 1 To 4
          For j = 0 To UBound(Phrases(i))
            Phr = Phrases(i)(j)
            LenPhr = Len(Phr)
            pos = 0
            Do
              pos = InStr(pos + 1, cVal, Phr)
              If pos > 0 Then c.Characters(Start:=pos, Length:=LenPhr).Font.Bold = True
            Loop Until pos = 0
          Next j
        Next i
      End If
    Next c
    Application.ScreenUpdating = True
  End If

End Sub

Thanks again for all the help with this report, it has and will save me valuable time for the foreseeable future.

 

[Peter_SSs]

Just one thing with your code: If you alter any of the cells in W11:Z11, the code will not process those changes. Is that a problem for you? It could be fixed.

 

[Mlogs]

You are correct about it not properly processing changes in W11:Z11. I have attempted to make it process the changes but I have not had any luck with it. If you could show me how to fix it so it does process these cells automatically that would be awesome.

Thank you.