Solved Excel Macro To BOLD Certain Words In A Line Of Text

February 16, 2018 at 11:29:48
Specs: Windows 10
I have an Excel Macro that colors certain words in a line of text (one that I got on this forum). But I would like it to also bold those same words. Here is the macro.

'Set Range to search
With Sheets("THOR").Range("E1:E25000")

'Loop through Banned Words list
For Each word In Sheets("Keywords").Range("IBM_KEYWORDS")

'Find cells with at least one Banned Word
Set w = .Find(word, lookat:=xlPart)
If Not w Is Nothing Then
firstAddress = w.Address
Do

'Find Banned Word within cell, force uppercase
'to eliminate case sensitivity
bWordStart1 = InStr(1, UCase(w), UCase(word))

'Set Banned Word font color to Blue
w.Characters(Start:=bWordStart1, _
Length:=Len(word)).Font.ColorIndex = 4

'Set up variable to search for more Banned Words within same cell
bWordStart_nxt = bWordStart1 + 1

'Search for more Banned Words in same cell, color each one red
Do
bWordStart_n = InStr(bWordStart_nxt, UCase(w), UCase(word))
If bWordStart_n > 0 Then
w.Characters(Start:=bWordStart_n, _
Length:=Len(word)).Font.ColorIndex = 3
bWordStart_nxt = bWordStart_n + 1
End If
Loop While bWordStart_n <> 0

'Search for next cell with same Banned Word
Set w = .FindNext(w)
Loop While Not w Is Nothing And w.Address <> firstAddress
End If

'Begin Search for next Banned Word in list
Next
End With
End Sub


See More: Excel Macro To BOLD Certain Words In A Line Of Text

Reply ↓  Report •

#1
February 16, 2018 at 12:03:42
✔ Best Answer
Before I address your question, I'd like to point out a few things...

1 - Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

2 - When posting code, please make sure you post a complete macro so that we know what we are working with. The code you posted doesn't start with any type of "Sub" line so we don't know if this is a standard macro or an event macro or a UDF, etc. The more work you make us do to figure out what is going on, the less energy we have to expend on your actual question. ;-)

3 - One of the Comments in the code says 'Set Banned Word font color to Blue but the instruction uses ColorIndex = 4.

4 is Green, not Blue. If you made changes to the original code, you should update the comments also.

As far as your question:

Change the 2 instructions that set the color to look more like this:

''Set Banned Word font color to Green, Font to Bold
  With w.Characters(Start:=bWordStart1, Length:=Len(word))
     .Font.ColorIndex = 4
     .Font.Bold = True
  End With

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •
Related Solutions


Ask Question