Hello. Thanks to Bob Phillips, I have the code below. It is a worksheet
change event, and when the target value is "Closed", it takes the cell to
the right of that, and finds that call contents on another sheet called HR
DB, and clears contents of any cell with that value.

I wold like to add one small (I think) thing, and am hoping someone could
help me with that. So here's an example. In sheet1, the target field is
set to "closed". The cell to the right of that has contents 1010. The code
then goes to HR DB, finds any cell with 1010 in it, and clears contents. I
would also like it to enter Now() in column A of every cell it clears
contents from. So, if it finds 1010 in cell G54, I would like it to also
add now() to A54. Thank you in advance!!


Private Sub Worksheet_Change(ByVal Target As Range)
'Placed in Sheet1
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Value = "Closed" Then
ClearCells .Offset(0, 1).Value
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub ClearCells(val)
Dim cell As Range

With Worksheets("HR DB")
For Each cell In .Range("K5:BH1000")
If cell.Value = val Then
cell.Value = ""
'***Maybe something here???***
End If
Next cell
End With

End Sub

Private Sub ClearCells(val)
Dim cell As Range

With Worksheets("HR DB")
For Each cell In .Range("K5:BH1000")
If cell.Value = val Then
cell.Value = ""
.Cells(cell.row,"A").Value = Now
End If
Next cell
End With

End Sub

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Hello. Thanks to Bob Phillips, I have the code below. It is a worksheet
change event, and when the target value is "Closed", it takes the cell to
the right of that, and finds that call contents on another sheet called HR
DB, and clears contents of any cell with that value.

I wold like to add one small (I think) thing, and am hoping someone could
help me with that. So here's an example. In sheet1, the target field is
set to "closed". The cell to the right of that has contents 1010. The
code
then goes to HR DB, finds any cell with 1010 in it, and clears contents.
I
would also like it to enter Now() in column A of every cell it clears
contents from. So, if it finds 1010 in cell G54, I would like it to also
add now() to A54. Thank you in advance!!


Private Sub Worksheet_Change(ByVal Target As Range)
'Placed in Sheet1
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Value = "Closed" Then
ClearCells .Offset(0, 1).Value
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub ClearCells(val)
Dim cell As Range

With Worksheets("HR DB")
For Each cell In .Range("K5:BH1000")
If cell.Value = val Then
cell.Value = ""
'***Maybe something here???***
End If
Next cell
End With

End Sub