how to add counter and change replace to delete cells?

[Mike11]

Hi, I have a macro which will search a document in specific range and find specific cells if found = replaced to data from my list ...

I want to change it to: if found = delete cell + delete 4 cells on left...

+
at the end i want to see msg bog saying how many time it find the spec cells... (not how many cells has been deleted.

Sub Find()
Dim myList, myRange
Set myList = Sheets("sheet3").Range("A1:A10")
Set myRange = Sheets("sheet3").Range("D1:F100")
For Each cel In myList.Columns(1).Cells
myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
Next cel
End Sub


Thank you

[JapanDave]

Makes no sense. What are you searching for? And what are you replacing with?

[Mike11]

I am searching for data from sheet 3 A1:a10
and replacing data in sheet 3(or different) in range D1:F100

-so for example if i have on sheet3 in A1= Car and B1= Blue
it will look at D1:F100 and replace every word Car to word Blue..... if there is no blue and its blank it will replace Car to blank cell


What i would like is to instead of replacing delete the cell where is car and also delete 4 cells on the left of Car

At the end to display how many times we found word Car and clear/ deleted this cell...

Hope its better now