Highlight Specific Text Within a Cell of Other Text

If you are willing to use VBA, you could use code like this, perhaps using a worksheet change event?

<font face=Courier New>  <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Set</SPAN> Rng = ActiveCell<br>  <SPAN style="color:#00007F">Dim</SPAN> S <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  S = "Take 5 PPE Swabs per Area, Both Shifts."<br>  <SPAN style="color:#00007F">If</SPAN> InStr(Rng.Value, S) > 0 <SPAN style="color:#00007F">Then</SPAN> Rng.Characters(1, Len(S)).Font.Color = 15</FONT>

Without VBA, I am not aware of a way that you can use Conditional Formatting to highlight part of a cell text.

Yes! VBA is great, but I don't know what to do in Excel after I've put the code in VisualBasic to make it work??

Do you want to highlight just the one string, "Take 5 PPE Swabs per Area, Both Shifts"

Others?

What range of cells do you want this highlighting to take place.

When should it take place? When a cell changes? When you press a button?

Could you explain a little more about what precipitates the highlighting and what cells get highlighted.

And, similarly, can I have a formula set up like:
="Water Samples: " & C3 & ". (Test for Chlorine and leave both samples in the Lab for testing.) " & C4 & " Shift Only"

but get a result with the following formatting (bold/underline)?
Water Samples: Handwash Sink. (Test for Chlorine and leave both samples in the Lab for testing.) 1st Shift Only

Okay, the highlighting: There would be about 8 specific text strings that would need highlighted. Not in any particular range and the rows that the highlighted text is in would be cut and pasted around the sheet each week and need to hold onto the highlighting. The highlighting would always remain in place on these 8 text strings. Therefore, it'd be a one-time formatting, but like I said, the rows that contain these strings will be cut and pasted and moved around on the sheet. Does this help?

I would add the following in a Module

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> HighlightCodes()<br>  <SPAN style="color:#007F00">' Select Cells to be highlighted and Run this Sub.</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> Codes(1 <SPAN style="color:#00007F">To</SPAN> 8)<br>  <SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> StartPos <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>  Codes(1) = "Take 5 PPE Swabs per Area, Both Shifts."<br>  Codes(2) = "Code 2"<br>  Codes(3) = "Code 3"<br>  Codes(4) = "Code 4"<br>  Codes(5) = "Code 5"<br>  Codes(6) = "Code 6"<br>  Codes(7) = "Code 7"<br>  Codes(8) = "Code 8"<br><br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Rng <SPAN style="color:#00007F">In</SPAN> Selection.Cells<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 8<br>       StartPos = InStr(Rng.Value, Codes(i))<br>       <SPAN style="color:#00007F">If</SPAN> StartPos > 0 <SPAN style="color:#00007F">Then</SPAN> Rng.Characters(StartPos, Len(Codes(i))).Font.ColorIndex = 15<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>  <SPAN style="color:#00007F">Next</SPAN> Rng<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Modify the "Codes" to match your strings.

Select the Cells to be highlighted and run the code.

If you then drag cells around manually the formatting should stay the same.

That is SO close. The only thing is that it changes the color of the font instead of behind the font. The font would actually remain black with gray highlighting behind it.

It is not possible to color part of the background of a cell. The entire background is highlighted or none. You change the color of individual characters, but I am not aware of a way to format a cell with more than one color.

Oh. Darn. Okay, well, your code will come in handy for other things so thank you much!!