MACRO TO FIND SUBSTRING OR SUBTEXT !

[jay dean]

Each cell in Range("B2:B5000") contains an alphanumeric word. I need a
*macro* that will loop through every cell in the range. If any word in
any cell in the range contains the substring or subtext "LL7P" then that
cell is to be colored red.

Any help would be greatly appreciated.

Thanks.
Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

For Each cell In Range("B2:B5000")
If cell,Value Like ("*LL7P*") Then
cell.Interior.Colorindex = 3
End If
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[jay dean]

Bob - This works excellently, and thanks a lot !
I have a follow-up question and a general one.

1. In the line "If cell.Value Like ("*LL7P*").." what is "Like"? Is it a
property of the cell object?.. and how do I use the VBA help or other
resources to learn these things?

2.When/How is the ampersand "&" used in cell formulas, or concatenating
text in VBA?

Thanks for all that you do!
Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Jay,

No, Like is an operator. Essentially, it allows a wildcard test by saying
*LLP7*. Take a look at 'Like Operator' in VBA Help. UN fortunately, VBA Help
is like any other Help, it assumes you know what you are looking for, it is
of no help if you don't know how to express you question.

I don't understand the second question. Your question seems to articulate it
perfectly, & is used to concatenate strings.

--

HTH

RP
(remove nothere from the email address if mailing direct)