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

Attraktor

New Member
Joined
Oct 26, 2009
Messages
31
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hello Marc,

I think even in Excel 2007 it is still an all or nothing proposition. You can change the Font Color of the individual letters using VBA.

Sincerely,
Leith Ross
 
Upvote 0
You cannot do that with Conditional Formatting, or upon values returned by formulas.

If the string in cell A1 is a constant, such as if you typed in or pasted
"The pink elephant has blue and green spots"
and you want only "blue" (as entered in cell B1 for that purpose), you'd need VBA, example:

Code:
Sub Test1()
Dim strString$, x&
strString = Range("B1").Value
With Range("A1")
.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
End Sub

Note, if the string is
"The pink elephant has blue and green spots and is haunted by Bluebeard's ghost"
then the string would have both "blue" entries turn blue, example

"The pink elephant has blue and green spots and is haunted by Bluebeard's ghost"
 
Upvote 0
Thank you both, Leith and Tom for your replies.

Yeah, I wasn't sure if I missed something in the help files for Excel or not.

I was hoping that this could be done without "resorting" to VBA and keep things as simple as possible.

Tom, thanks for the time and work you spent on the VBA solution. I will keep this in mind, should I decide to the extra mile and use VBA in my sheet.


This site and all it's helpful and gifted members rock!

Thanks again guys.
 
Upvote 0
Hi everyone,

I find the VBA code provided by Tom Urtis very useful. I tried to extend this code in VBA, so that this rule will be applied in my whole sheet, but unfortunately I cannot make it work. Could you please provide me with advice?

Thanks in advance!
 
Upvote 0
Case solved. A colleague of mine simply introduced the offset function into the code:

Sub Test1()
Dim strString$, x&
For i = 0 To 3130
strString = Range("B1").Offset(i, 0).Value
With Range("A1").Offset(i, 0)
.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 i

End Sub

Nice forum though. Think I'm going to check this more often!
 
Upvote 0
Guys - I know it was too hard to think about this solution but here you go with the solution to highlight certain word in cell :)
1) Open Excel Sheet
2) Insert Text Box
3) Move the text box to the word you want to highlight it
4) remove text box outline
5) fill the box color as you wish
6) reduce the box transparency so that your highlighted word appears
7) Copy and past the box to the other words you wish to highlight having the text box size modified according to the word length and so on....

Your feedback really matters!
BR,
Mohamed Hassaan From Egypt
 
Last edited:
Upvote 0
You cannot do that with Conditional Formatting, or upon values returned by formulas.

If the string in cell A1 is a constant, such as if you typed in or pasted
"The pink elephant has blue and green spots"
and you want only "blue" (as entered in cell B1 for that purpose), you'd need VBA, example:

Code:
Sub Test1()
Dim strString$, x&
strString = Range("B1").Value
With Range("A1")
.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
End Sub

Note, if the string is
"The pink elephant has blue and green spots and is haunted by Bluebeard's ghost"
then the string would have both "blue" entries turn blue, example

"The pink elephant has blue and green spots and is haunted by Bluebeard's ghost"

Hi Tom,
This code is perfect for what I am trying to do and working really well.
I know nothing about VBA but am amazing at copying and pasting code from the internet.... Could you help me convert the cell references to ranges? As in, I don't just want to look in cell "B1" but rather the entire column G:G (tried "G:G" but it didn't like it!)
Thanks
Hannah
 
Upvote 0
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,

Rich (BB code):
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
 
Upvote 0
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,

Rich (BB code):
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

Works PEFECTLY! Thank you
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top