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

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

I am inept, to say the least, when it comes to VBA, but I THINK that this may be just what I am looking for.

I have an Excel doc that I have built with an if then argument that says 'if a selected field is equal to another, then the impacted field will be equal to another'.
=IF(I2=Q5,S5,IF(I2=Q2,S2,IF(I2=Q3,S3,IF(I2=Q4,S4,IF(I2="","",IF(I2=Q6,S6, IF(I2=Q7,S7,IF(I2=Q8,S8,IF(I2=Q9,S9,IF(I2=Q10,S10,IF(I2=Q11,S11,IF(I2=Q12,S12,IF(I2=Q13,S13,IF(I2=Q14,S14,IF(I2=Q15,S15)))))))))))))))

I'm sure that there's a better way to do this, but this is as far as my self- taught Excel skills go. That's not my issue though. In my "S" column, there are some font differences (color, bold). How can I get those to come over to my “L” column so that when I get my final product they are as I entered them? Basically, every time that an if then has the word “insert”, I’d like for the word itself to be bold and red. Or better yet if all font between the < > (or other usable characters) would be bold and red. Or just red even.
Example:
Upon review, enrollment for <insert enrollee or enrollees> was located. We have processed the records and confirmed that the premium amount is <insert premium amount>.
 
Upvote 0

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.
Sorry. Example should be:

Upon review, enrollment for *insert enrollee or enrollees* was located. We have processed the records and confirmed that the premium amount is *insert premium amount*.

The text between the asterisks is what I would like to be bold red.
 
Upvote 0
Does anyone know if this has been changed in the newer versions of Excel?
Welcome to the MrExcel board!

This thread is quite long so I am not sure which part(s) of it you are referring to. If you are referring to the very first post then the answer is "no" you still cannot format parts of a cell's text using Conditional Formatting. vba would be required so if macros are not allowed then I think that you would not be able to achieve what you want.
 
Upvote 0
.. I THINK that this may be just what I am looking for.
Welcome to the MrExcel board!

I'm not sure that I fully understand your requirement, but I think you are saying that you have a formula in a cell and want to format part of the cell's content that the formula returns.
If that is what you want & the formula is to remain in the cell then that is not possible with either Conditional Formatting or a macro.
 
Upvote 0
Thanks so much Peter_SSs! I am sorry for the bad communication, I was referring to using formulas to highlight a portion of a cell instead of the whole cell.


This thread is quite long so I am not sure which part(s) of it you are referring to. If you are referring to the very first post then the answer is "no" you still cannot format parts of a cell's text using Conditional Formatting. vba would be required so if macros are not allowed then I think that you would not be able to achieve what you want.
 
Upvote 0
Hi, great thread! Is there a way to have it search and highlight multiple cells??? for instance if I have 10 key words I would like it to search for it can do that all at once but I can choose different colors for each words or a bold for certain ones?? Thanks
 
Upvote 0
Hi!
I tried to create my code based on the information I gathered so far in this thread, but I won't be able to do so :$ I'm hoping that someone can help me out?

I have long texts in Column A, and I have a serious of strings (700+ and counting) in column B. I'm looking for a way to highlight all the words in Column A, based on the strings on Column B, without differences of capital/non capital letters.

Example Column A
A long time ago there was once a poor boy called **** Whittington
who had no Mummy and Daddy to look after him so he was often
very hungry. He lived in a little village in the country. He’d often
heard stories about a far away place called London where everybody
was rich and the streets were paved with gold. **** Whittington
was determined that he would go there and dig up enough gold
from the streets to make his fortune. One day he met a friendly
waggoner who was going to London who said he would give him
a lift there, so off they went. When they reached the big city ****
couldn’t believe his eyes, he could see horses, carriages, hundreds
of people, great tall buildings, lots of mud, but nowhere could he
see any gold. What a disappointment, how was he going to make
his fortune? How was he even going to buy food?

Example Column B
gold
fortune
often
daddy

Thank you!
 
Upvote 0
Hi Peter_SSs!

Maybe I was over-complicated it :)

By serious of strings I meant that there is a lot, over 700, words as in Column B example:

Example Column B

gold
fortune
often
daddy

These word I would like to be highlighted in this text in Column A:
Example Column A
A long time ago there was once a poor boy called **** Whittington
who had no Mummy and Daddy to look after him so he was often
very hungry. He lived in a little village in the country. He’d often
heard stories about a far away place called London where everybody
was rich and the streets were paved with gold. **** Whittington
was determined that he would go there and dig up enough gold
from the streets to make his fortune. One day he met a friendly
waggoner who was going to London who said he would give him
a lift there, so off they went. When they reached the big city ****
couldn’t believe his eyes, he could see horses, carriages, hundreds
of people, great tall buildings, lots of mud, but nowhere could he
see any gold. What a disappointment, how was he going to make
his fortune? How was he even going to buy food?

W
ithout differences of capital/non capital letters I meant that in Column B for example: "daddy"; it still will highlight "Daddy" in Column A

Sorry for the misunderstanding :$
 
Upvote 0
Without differences of capital/non capital letters I meant that in Column B for example: "daddy"; it still will highlight "Daddy" in Column A
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.
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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