Nevermind, I fixed the problem by switching to Mid statements:

For k = 1 To L - 1

position1 = Mid((rng.Cells(1, k) & rng.Cells(1, k + 1)), 1, 4)
position2 = Mid((rng.Cells(1, k) & rng.Cells(1, k + 1)), 2, 4)
position3 = Mid((rng.Cells(1, k) & rng.Cells(1, k + 1)), 3, 4)

And then If-thens to evaluate.

Everything's working now.

Matt

"chemdude77" wrote:

Actually, I may have gotten excited too early as my code is failing now. The
problem is when the FIND command doesn't find the string the code crashes
with error 1004: Unable to get the Find property of the WorksheetFunction
class. Any idea how to fix this? Here is relevant code so far:

position = Application.WorksheetFunction.Find("AYRT", rng.Cells(1, k) &
rng.Cells(1, k + 1))

And then I have If-Then statements to evaluate the strating position of the
string and color it accordingly. Final statement is an Else to start back at
the top if the string doesnt exit, but this is the part that doesn't work.
Any way to have it move on to the next k value if the string isn't found?

Thanks

Matt



"chemdude77" wrote:

Hi Scott,

Thanks for the reply this is exactly what I'm looking for!

Matt

"ScottO" wrote:

How about something like
=FIND("DRDY",A6&B6&C6), which returns the value 2 in your example.
If "DRDY" is not found then the formula will return Value#.
hth
ScottO

"chemdude77" wrote in message
...
| Hi,
|
| If I have data in cells such as YDR DYH VNB etc, in separate cells,
and I
| want to search for a pattern that can span multiple cells how do I
do this?
| Suppose I want to search the cells for the pattern DRDY (which
includes
| characters in cells 1 and 2) and color it when its found in the
above example?
|
| Matt