delete contents of cell

[RDC]

I have two questions, firstly I want to delete a cells value (it has a
numerical value in it) by typing the word "dead" into another cell.

Secondly, I have large spreadsheet with deals on, can I somehow run a macro
to remove all those deals with the "Dead" in the row to another spreadsheet?

Thanks

 

[Gary''s Student]

This is only the first part. Say we have data in column B. If we enter
"dead" in column A we want the adjacent cell in column B to be cleared. Put
this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set A = Range("A:A")
If Intersect(t, A) Is Nothing Then Exit Sub
If UCase(t.Value) = "DEAD" Then
Application.EnableEvents = False
t.Offset(0, 1).Clear
Application.EnableEvents = True
End If
End Sub

Say we have data in column B and already have some "dead"s in column A and
we want to do some cleanup. Run this macro:

Sub oldData()
n = Cells(Rows.Count, 1).End(xlUp).Row
Application.EnableEvents = False
For i = 1 To n
If UCase(Cells(i, 1).Value) = "DEAD" Then
Cells(i, 2).Clear
End If
Next
End Sub