Test this on a copy of your data:

Sub subFindRed()
Dim sAddr As String
Dim rng As Range, rng1 As Range
Set rng = Cells(Rows.Count, 1)
Set rng1 = Range("A:B").Find(What:="Red", _
After:=rng, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng1 Is Nothing Then
sAddr = rng1.Address
Do
If rng1.Column = 1 Then
rng1.Offset(0, 1).Resize(, 3).ClearContents
ElseIf rng1 = 2 Then
rng1.Offset(0, -1).ClearContents
rng1.Offset(0, 1).Resize(, 2).ClearContents
End If
Set rng1 = Range("A:B").FindNext(rng1)
Loop While rng1.Address < sAddr
End If
End Sub


--
Regards,
Tom Ogilvy

"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