Addition to Worksheet Change event (RP?)

[Steph]

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

[Tom Ogilvy]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>