VBA: Bold Certain Text in Cell

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Hi I was looking all over for a simple script to bold only certain text within cells. I tried to record a macro, but it keeps bolding all text within each cell.
I only want a phrase within the cell. For example: "blah blah blag and to "THE MOON".
I found a script but it has that dreaded popup box to enter the text each time.
Can someone help me? Thank you.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Assume cell A2 contains a string of several words including "the moon".

VBA Code:
Sub t()
Dim ch As Long
ch = InStr(Range("A2"), "moon") - 4
If Mid(Range("A2").Value, ch, 3) = "the" Then
    Range("A2").Characters(ch, 8).Font.Bold = True
Else
    Range("A2").Characters(ch + 4, 4).Font.Bold = True
End If
End Sub
 

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

JLGWhiz Thank you for posting so quickly. If I add other text, it does not find the words. Can I modify it just bold the last line of the cell?​

Sorry about the bold text. It won't turn off.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
The method I used would only find the first occurence of the word 'moon' and then use that as a marker to get a leading or trailing word. Offhand, I don't know of a method to find all occurences in a string and I am not inclined to do a lot of research on it. sorry.
Regards, JLG
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
If I add other text, it does not find the words. Can I modify it just bold the last line of the cell?
Please give us a more varied set of sample data (say 8-10 rows) with XL2BB and explain what results you want in relation to that sample data.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,625
Messages
5,625,946
Members
416,143
Latest member
JoyceMB

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