Hi, I have been putting together a program for the past
year in vb/excel and I am stuck on the last macro to put
this whole thing to bed. Here is the problem:
On one worksheet there is a range of cells that has
comments. cell(A1) (ei "3wd, rounding corner")
I need to look into each cell and determine whether a
portion of the cell contains certain words or abreviations
(ie "3wd","steadied","std","5wd") and then assigning a
variable a value.
I have tried making a function with TextCompare but keep
running into errors. Recording a "Find" macro puts me
into another whirlwind. It seems to easy to be this hard.
Either I am getting old or losing my mind. Thank you in
advance for your help.

This is untested. Watch for linewrap. Not sure whether you are
searching comments or cells. Also not sure how many cells you wish to
search. I have no idea what you mean by 'then assigning a
variable a value'. But this should push you in one direction.

Sub FindPhrase()

Dim intI as Integer
Dim rngC as Range, rngCom as Range
Dim rngFound as Range
Dim varArray As Variant

varArray = Array("3wd","steadied","std","5wd")
Set rngCom = Range(YourRangeToSearch)

For intI = LBound(varArray) To UBound(varArray)

For Each rngC In rngCom
Set rngFound = rngC.Find(What:=varArray(intI),
Lookat:=xlPart,Lookin:=xlFormulas)
' Set rngFound = rngC.Find(What:=varArray(intI),
'Lookat:=xlPart,LookIn:=xlComments)
If Not rngFound Is Nothing Then
rngC.Font.ColorIndex=3
End If
Next rngC

Next intI

End Sub

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
Hi, I have been putting together a program for the past
year in vb/excel and I am stuck on the last macro to put
this whole thing to bed. Here is the problem:
On one worksheet there is a range of cells that has
comments. cell(A1) (ei "3wd, rounding corner")
I need to look into each cell and determine whether a
portion of the cell contains certain words or abreviations
(ie "3wd","steadied","std","5wd") and then assigning a
variable a value.
I have tried making a function with TextCompare but keep
running into errors. Recording a "Find" macro puts me
into another whirlwind. It seems to easy to be this hard.
Either I am getting old or losing my mind. Thank you in
advance for your help.