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]

rikrak

Shouldn't I be able to right the statement like this and it look at colomn A - "startPos = InStr(["a1:a100"], searchText)"

Right it appears it only looks at cell a1??

 

[rikrak]

em,

It's very easy.
1. Select the cells (range) you want to process. This can be the current cells you have selected (Selection) or a range you specify in the code.
2. Loop through all cells in the range and do the painting.

In your case the cells in column A contain the text to color.
You have to specify what text you are looking for. This can be done in the code, in a cell or inputbox.
Most flexible is in a cell.

<pre>
Sub colorText()
Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String

' specify text to search. (Change to your needs)
' searchText = [A1]
' searchText = Application.InputBox("enter search text")
searchText = "brown fox"

' loop trough all cells in selection/range (Change to your needs)
'For Each cl In Range("A:A")
'For Each cl In Selection
'For Each cl In Range("A1", Range("A65536").End(xlUp))
For Each cl In Range("A1:A100")

totalLen = Len(searchText)
startPos = InStr(cl, searchText)

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

 

[itr674]

rikrak - I knew it had something to do with defining a range-I just kept leaving out a couple of things-thanks again ...

 

[Jack in the UK]

Em

My code will convert colour where ever the text is in the SHEET, no restrictions

NAMES are the cell you put the words to colour and and colours the colour you want.

the codes is compleate all you need to do is fillin you selections and away you go no edits required

Let me know ill email you a working sheet and you see its wonderful!!

HTH
Jack

 

[itr674]

Jack In UK - yea, go ahead and send me a copy if it is not to big--see my profile.

 

[itr674]

How could this code be enhanced to show color on several different words, like "brown fox", or "red squirrel", or "pink rabbit"?

Sub colorText()
Dim cl As Range
Dim startPos As Integer
Dim totalLen As Integer
Dim searchText As String

' specify text to search. (Change to your needs)
' searchText = [A1]
' searchText = Application.InputBox("enter search text")

searchText = "brown fox"

' loop trough all cells in selection/range (Change to your needs)
'For Each cl In Range("A:A")
'For Each cl In Selection
'For Each cl In Range("A1", Range("A65536").End(xlUp))

For Each cl In Range("A1:A100")
totalLen = Len(searchText)
startPos = InStr(cl, searchText)

If startPos > 0 Then
With cl.Characters(startPos, totalLen).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
End If

Next cl
End Sub

 

[itr674]

Is there a way to make this some sort of "OR" statment.......searchText = "brown fox", "red squirrel", "pink rabbit"?

 

[itr674]

Why is the red text turning showing red in VBA editor?
I am trying to color multiple different word red in cells down a range.

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

 

[itr674]

Does anyone know if Jack in the UK is still around?

 

[Rick Rothstein]

Does anyone know if Jack in the UK is still around?

Hard to say... the thread you are referencing is from 2002 and his last posting on this forum was January 7, 2015 (although his last activity here was June 11, 2015).