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