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

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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."
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
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

Active Member
Joined
Aug 21, 2002
Messages
255
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

Well-known Member
Joined
Feb 16, 2002
Messages
3,215

ADVERTISEMENT

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

Active Member
Joined
Aug 21, 2002
Messages
255
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

Well-known Member
Joined
Feb 16, 2002
Messages
3,215

ADVERTISEMENT

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

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
rikrak - how do I make your code do column A1:A100? I tried several things but kept getting break in code ...
 

itr674

Well-known Member
Joined
Apr 10, 2002
Messages
1,778
Jack in the UK - where in the code do I put "brown fox", or the word I want to bold/color??
 

Forum statistics

Threads
1,148,181
Messages
5,745,204
Members
423,933
Latest member
ankushmukherjee

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
Top