Cells - is there code to bold and color word(s) in a cell?

[itr674]

I have copied numerous paragraphs into cells. I need to bold and color certain words in the document (excel) so they stand out.

There are numerous sentences in one cell, not just one word. How would I highlight "brown fox" in all the cells in column C if there were sentences similar to this:

"The quick brown fox jumped over the hen house."

 

[itr674]

Rick Rothstein thanks for reply.
Yes this is old thread and I am back to trying to use it with multiple words needing highlighting, and am just now realizing his array is probably what I need but am having problems with it.
I think he sent me a sample file but can't find it so was hoping he might still be around to assist...

 

[itr674]

I don't believe it, I changed Names to JackintheUK_Names and it is working although without selecting a short range it is taking 30 seconds to run...I'll have to come up with a range area...

Option Explicit
Option Base 1

Sub Jack_Colour_TExt()
Dim Target As Range
Dim JackintheUK_Names As Variant
Dim Colours As Variant
Dim x As Integer
Dim y As Integer
Dim z As Long

Set Target = Selection

JackintheUK_Names = Array([a1], [a2], [a3])
Colours = Array([b1], [b2], [b3])

For x = 1 To UBound(JackintheUK_Names)
For z = 1 To Target.Cells.Count
y = InStr(1, Target.Cells(z).Value, JackintheUK_Names(x))
If y > 0 Then
With Target.Characters(Start:=y, Length:=Len(JackintheUK_Names(x))).Font
.ColorIndex = Colours(x)
.FontStyle = "Bold"
End With
End If
Next z
Next x

End Sub

 

[itr674]

Can an Array be run horizontal instead of vertical?
I need to have the search names in row 1 and the colors in row 2.

 

[itr674]

Well I changed the X and Y around and thought I had it but it is not working perfectly, it is missing some words and highlighting others that are not in the list to highlight...???

Option Explicit
Option Base 1


Sub Jack_Colour_TExt()
Dim Target As Range
Dim JackintheUK_Names As Variant
Dim Colours As Variant
Dim x As Integer
Dim y As Integer
Dim z As Long


Set Target = Selection


JackintheUK_Names = Array([i1], [j1], [k1], [l1], [m1], [n1])
Colours = Array([i2], [j2], [k2], [l2], [m2], [n2])


For y = 1 To UBound(JackintheUK_Names)
For z = 1 To Target.Cells.Count
x = InStr(1, Target.Cells(z).Value, JackintheUK_Names)
If x > 0 Then
With Target.Characters(Start:=x, Length:=Len(JackintheUK_Names)).Font
.ColorIndex = Colours
.FontStyle = "Bold"
End With
End If
Next z
Next y
End Sub

 

[István Hirsch]

To format substrings in cells in a single step, have a look at this:

https://www.youtube.com/channel/UCSSpwcqOEo_xNqWLFMg9YRQ

 

[itr674]

To format substrings in cells in a single step, have a look at this:

https://www.youtube.com/channel/UCSSpwcqOEo_xNqWLFMg9YRQ

Interesting, but I am getting no sound. I think I found the web site and am studying now...

 

[Jack in the UK]

Hi yes I am still very 'arround' not so often on here, I do drop by

my inbox is full thou, rom 2005 hummmm (I used to have unlimited, them it was taken away from me, so i just left it)

This is very old code and post, A lot of my codes have the name Jack in it and / or jiuk and/ or JackInTheUk etc - in code you can make a lot of it up provided you code it correctly (structured)

Some used to change and edit my codes if i did this, so changing it back does dot surprise me

jiuk

 

[itr674]

Looks like the site is gone??

Well, back to the drawing board...

 

[itr674]

It is old, as am I now, and I am the original requester, but first, hope you are well?

Had to change the words to search for to run horizontally instead of vertical.
Seems to work on a small sample but looking down about 5000 rows with some cells having 300+ characters it is having trouble highlighting the correct terms, mostly like my issue...

The terms I am searching for are something like T9, T15, T248, T240, T203...
I have the term isn range [i1], [j1], [k1], [l1], [m1], [n1] and color code in
[i2], [j2], [k2], [l2], [m2], [n2]

 

[Jack in the UK]

Yeah I am fine, that was a long time ago 13 years or so. I kinda remember this path and I used the same kind of code, I have hardly programmed in years now. Maybe I should have a play.
Someone told me theres Office 2016 out now, blimey last I knew it was 2010. and Win Se7en, but hey I need to catch up

Trust you are well and all are on here

jiuk