I gave you a solution that will do this. You just need to change

Set rng1 = Range("A:B").Find(What:="Red", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)


to

Set rng1 = Range("A:B").Find(What:="MEAD", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)



--
Regards,
Tom Ogilvy


"Lolly" wrote in message
...
Hi,
Mike

I am not only looking for Red. I am looking for some other words also. It
's
not necessary to match with colB or ColA

I will give u e.g
ColA ColB ColC ColD

Hred Mead GHM MHG GH
HG JH MEAD HJ LK
GH NG MJ KJ
GH MJ NG MJ


Ideally macro should search for mead which is a part of text in the cell
and
then clear contents next to it and before that. Here is what it should
look
like after macro is run


ColA ColB ColC ColD

Hred Mead
JH MEAD
GH NG MJ KJ
GH MJ NG MJ


I hope this clarifies your question. Thanks a lot mike.
This I need to do for thousands of rows.





--
Kittie


"Mike Fogleman" wrote:

Some clarification needed. Are we only looking for "Red" or do you want
to
compare every value in column A for a match in column B and then clear
the
cells you said? If there is no match in column B then leave the adjacent
cells to column A as is?

Mike F
"Lolly" wrote in message
...
Hi,
all

I have a data like this.
ColA ColB ColC ColD
Red HG VG WH
Yellow T G Y
they Red G Y
Yellow DG MG GH


I want to create a macro which finds Red in colA and ColB. If it finds
in
colB then next two cell should be replaced by null(Blank) value means
empty.
One cell before that in col A should also be replaced by null value.
Ifred
is in ColA then next two cell should have null(blank) value. Here ia
what
I
want my sheet to look like.

ColA ColB ColC ColD
Red
Yellow T G Y
Red
Yellow DG MG GH
Any help on this would be highly appreciated.

Thanks in advance
--
Kittie