VBA to Color a Single word (or Phrase) in a large population

[seeja001]

Hello - I'm new to the message board and NOT a programer (but I do naively dabble in VBA a bit). My task is simple: I have many cells in a sheet (~ 1500 lines) that may contain a word AND I want to color the word (or phrase) so as to easily spot it visually when reviewing the sheet. Cell Content varies from 0 < x < 1200 characters. This can be don manually, but it's a real pain. I've used a few routines that when executed, color ALL text contained within the cell rather than isolating the single word of phrase. My latest version modified some example code using a class module approach, but throws compiler errors for the "With/End With" or "For Each" constructs upon execution. Would appreciate any help from the more seasoned folks if possible. Thanks.

Class Module:

Option Explicit
Private pPhrase As String

Private Sub Class_Initialize()
Worksheets("Sheet1").Select
End Sub

Private Sub Class_Terminate()
End Sub

Public Property Let Phrase(Value As String)
pPhrase = Value
End Property

Public Property Get Phrase() As String
Phrase = pPhrase
End Property

Normal Module:

Option Explicit

Sub UsingColorWordClass()
Dim w As ColorWord
Dim r As Range
Set w = New ColorWord
Set r = Range("ab3:ab1500")
w.Phrase = "HOUSE"

'With w
' .Font.Bold = True
' .Font.Color = vbRed
'End With

For Each w In r
w.Color = vbRed
Next

Debug.Print w.Phrase
Set w = Nothing

End Sub

 

[Skyybot]

I believe that you are receiving the error because your ColorWord Class does not have Font properties.

 

[seeja001]

Thank for taking the time to review. It shows up in the object browser as a class, with members. As such, i thought i can just access it. All the examples I see just use it, looks so trivial. Would you have a fix with code statement and placement?

 

[Skyybot]

I cannot edit the current code to give the results that you have explained that you want. But I do have a question; If a cell contains 900 characters, and you only highlight one word, how would you see it if the column width doesn't allow for the full length of the string? It seems like highlighting the entire cell would be the way to go for quick visual checking.

 

[seeja001]

Ah - well I'm working on a Mac and the Office 365 version here allows to wrap text considerably. Don't know the max allowable but it's > 256 characters. I've measured 1120 so far in a single cell. And with the appropriate width, I can view the entire cell - so a RED colored BOLD word or phrase will stand out immediately, whereas if the cell text is all red (or red interior), I'm compelled to read all the non-essential info - not what I'm after. I can manually search for the word, color it red and leave all else as is - so it will, word (but manually), again, not what I'm after. - Thanks for trying though. I have 3 routines that color ALL text red - but ..... / Thanks again.

 

[seeja001]

Must I do anything to keep this thread alive so others can still see it and possibly respond?

 

[Skyybot]

Must I do anything to keep this thread alive so others can still see it and possibly respond?

The thread will stay alive until it has a Solution marked. Others will see it. In the meantime, I will keep working on a solution.

 

[Skyybot]

What are your Workbook and Worksheet names?

 

[Skyybot]

@seeja001 , What are your Workbook and Worksheet names?

 

[seeja001]

@seeja001 , What are your Workbook and Worksheet names?

@seeja001 , What are your Workbook and Worksheet names?

Sooo Sorry for my late response - I was out of the house today. The Workbook file name is "File-45_(M-Test).xlsm" without quotes; Worksheet name is default: tab name is "Sheet1" also without quotes.