Excel Macro to color certain phrases within a cell

September 28, 2017 at 10:57:08
Specs: Windows 7
I have a list of banned words and key phrases. I have a macro which will change the font of the word to red for those in the banned words list; however, when there are 2 words, i.e. "Red House" it is only changing the font for Red and it is doing so even if words such as "tired" just because "RED" is present.

Here is the code that I am currently using:

Public Sub Formatting()

Dim X As Long
Dim Position As Long
Dim Cell As Range
Dim Words As Variant
Dim Parts() As String

'Search Words
Words = Array("Red House", "Small")

'Identify specific range
For Each Cell In Range("Z2:EV20000")

If Len(Cell.Value) Then
For X = LBound(Words) To UBound(Words)
Parts = Split(Words(X))
If UBound(Parts) >= 0 Then
Position = InStr(1, Cell.Value, Parts(0), vbTextCompare)
Do While Position
With Cell.Characters(Position, Len(Parts(0))).Font
.Color = RGB(255, 0, 0) 'Color = Red
.Bold = True 'Bold Text
.Italic = True 'Italics Text
.Underline = True 'Underline Text
End With
Position = InStr(Position + 1, Cell.Value, Parts(0), vbTextCompare)
End If
End If

End Sub

Any help would be hugely appreciated!

See More: Excel Macro to color certain phrases within a cell

Reply ↓  Report •

September 29, 2017 at 03:38:51
First, a posting tip:

Please click on the How-To link at the bottom of this post and read the instructions on how to use the pre tags to format VBA code to make it easier for us to read. Then repost your code. Thanks!

As far as your question...

Take a look at the code in Response #3 of this thread. It's something I wrote a few years ago. I don't have to test it/modify it for your phrases, but maybe it will give you some ideas.


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

message edited by DerbyDad03

Reply ↓  Report •
Related Solutions

Ask Question