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

 

[phantom1975]

It depends on what version of Excel you are using. I believe you can do that in versions 2000 and higher. I'm not sure about the code, but I have been able to highlight individual words in a cell and change the properties of the word. I hope this helps.

 

[itr674]

phantom1975 - I'm using 2000, and I can change the format of individual words in the cell but doing it individually will take a lot of time ...

 

[rikrak]

Hi em,

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

<pre>
Sub colorText()

Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String

searchText = "brown fox"

totalLen = Len(searchText)
startPos = InStr([a1], searchText)

If startPos > 0 Then
With [a1].Characters(startPos, totalLen).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
End If
End Sub
</pre>

 

[Jack in the UK]

Hi i feel that the above works well but i wanted to share expand as ou might [LIKE JACK DOES] have a few to highlight

Try this the trick is to have the array to say teh selections ie
Names are you text to search and colour
Colour - erm does taht to that colour, i chose to have lower down on same sheet so i can edit easy, this is one of my MUST HAVES and in all my wok, i just add what i need,

Attach to any command button shold work OK!!

Dedicated to a friend, whos support towards me is second to no one, hes / shes top! and will know who they are when they read this post!! top bombing as we say in london.


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 Integer

Set Target = Selection

JackintheUK_Names = Array([C200], [C201], [C202], [C203], [C204], [C205], [C206], [C207], [C208], [C209], [C210], [C211], [C212])
Colours = Array([E200], [E201], [E202], [E203], [E204], [E205], [E206], [E207], [E208], [E209], [E210], [E211], [E212])

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

End Sub

 

[rikrak]

Hi Jack,

I made a copy if you don't mind.

But the code needs some modification:
Dim z As Long
and all arrayreferences of Names have to replaced by JackintheUK_Names

Henk

 

[Jack in the UK]

Hi rikrak

Sure copy away, im proud , your so very welcome, have fun, works well, pricate is better really, but though command would be better for this post

i ust admit i kind of throw this on the board - so YES your spot on

Thank you dearly i love to see others ideas and work, good input thats how i learn being self taught thus Jack different ways, thanks again buddy

Jack

 

[Jack in the UK]

to add
Dim z As Integer

Change to Dim Z as Long

YES i thank you i really must get ths habbit, i dislike BIG sheets i prefer loads of smaller ones, i thank you for the comment and stand corrected. Whats best for me works for others but thats not to say that always best for others = good point

Thank you again for this.

 

[itr674]

rikrak - how do I make your code do column A1:A100? I tried several things but kept getting break in code ...

 

[itr674]

Jack in the UK - where in the code do I put "brown fox", or the word I want to bold/color??