If I only had 2 or 3 names to check, I might use the "copy|paste" method of
writing code <bg.

But if I had more (or might grow into more), I think your use of arrays is much
better.

Just add a couple things to a couple of arrays and walk away happy!

Peter T wrote:

the modified version of Pete's code created
the colour and removed all of the contents of the cell

Earlier you added to you original objective -
"what i really do need now is once the
colour has been put in place i need to loose the name but keep the
variable in the cell"

Dave suggested two different methods within the modified version to do just
that

mycell.Value = Mid(mycell.Value, Len(vaNames(i)) + 1)
'or to remove any leading spaces
mycell.Value = Trim(Mid(mycell.Value, Len(vaNames(i)) + 1)

I'm sure he didn't intend you to include both methods in the same code, but
for you to try each and use the one that best suits your needs, namely
delete the search string from the cell leaving other contents in place. Try
each with a comment in front of the other and take your pick. Altogether
Dave has given you lots of methods which is why he is indeed as you say a
legend <vbg

Regards,
Peter

"Simon Lloyd"
wrote in message
...

Dave your quickly becoming a legend in my estimation!, your second code
that you provided that trimed the name off and left whatever else was
in the cell worked a treat, the modified version of Pete's code created
the colour and removed all of the contents of the cell.......so i'm
sticking with yours!

I must try to learn a little about searching or comparing text in any
case as excel always looks for exactly how you typed it in the code.

Thanks very much for your time and trouble!

Regards,
Simon


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=552061


--

Dave Peterson