Good afternoon. On Sheet2 of my workbook, I have a table that drives a data
validation list on sheet1. Sheet2 Col A has the status of Open or Closed.
Col B has the validation item list. So on sheet1, the list is only
populated with the Open items.

What I am trying to do is this - if the status of an item on sheet2 is
changed to Closed (and therefore removed from the validation list), how can
I find that item on Sheet1 and delete it in every cell that it occurs in? I
have heard of a worksheet change event, but not familiar with coding it.
Thank you in advance.

Not too clear on what you need, but here is some change event code as an
example

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Good afternoon. On Sheet2 of my workbook, I have a table that drives a
data
validation list on sheet1. Sheet2 Col A has the status of Open or Closed.
Col B has the validation item list. So on sheet1, the list is only
populated with the Open items.

What I am trying to do is this - if the status of an item on sheet2 is
changed to Closed (and therefore removed from the validation list), how
can
I find that item on Sheet1 and delete it in every cell that it occurs in?
I
have heard of a worksheet change event, but not familiar with coding it.
Thank you in advance.

Thanks Bob. Wasn't too clear, was I? In a nutshell, sheet2 looks like
this:
Status Item
Open 7
Open 9
Closed 11
Open 10
Open 4

Sheet1 has a table full of "Items".
7 9 11
9 11
4 10 11 7
9 11 9
10 7 9 11

If I go to sheet2 and change the status of Item7 to Closed, I want to go to
Sheet1, and find all instances of 7 and clearcontents of cell.

Thanks again.


"Bob Phillips" wrote in message
...
Not too clear on what you need, but here is some change event code as an
example

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Good afternoon. On Sheet2 of my workbook, I have a table that drives a
data
validation list on sheet1. Sheet2 Col A has the status of Open or
Closed.
Col B has the validation item list. So on sheet1, the list is only
populated with the Open items.

What I am trying to do is this - if the status of an item on sheet2 is
changed to Closed (and therefore removed from the validation list), how
can
I find that item on Sheet1 and delete it in every cell that it occurs in?
I
have heard of a worksheet change event, but not familiar with coding it.
Thank you in advance.

Hi Steph,

Hope that this does it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

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("Sheet1")
For Each cell In .UsedRange
If cell.Value = val Then
cell.Value = ""
End If
Next cell
End With

End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Bob. Wasn't too clear, was I? In a nutshell, sheet2 looks like
this:
Status Item
Open 7
Open 9
Closed 11
Open 10
Open 4

Sheet1 has a table full of "Items".
7 9 11
9 11
4 10 11 7
9 11 9
10 7 9 11

If I go to sheet2 and change the status of Item7 to Closed, I want to go
to
Sheet1, and find all instances of 7 and clearcontents of cell.

Thanks again.


"Bob Phillips" wrote in message
...
Not too clear on what you need, but here is some change event code as an
example

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Good afternoon. On Sheet2 of my workbook, I have a table that drives a
data
validation list on sheet1. Sheet2 Col A has the status of Open or
Closed.
Col B has the validation item list. So on sheet1, the list is only
populated with the Open items.

What I am trying to do is this - if the status of an item on sheet2 is
changed to Closed (and therefore removed from the validation list), how
can
I find that item on Sheet1 and delete it in every cell that it occurs
in?
I
have heard of a worksheet change event, but not familiar with coding
it.
Thank you in advance.

Amazing! Thank you!!

"Bob Phillips" wrote in message
...
Hi Steph,

Hope that this does it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

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("Sheet1")
For Each cell In .UsedRange
If cell.Value = val Then
cell.Value = ""
End If
Next cell
End With

End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Bob. Wasn't too clear, was I? In a nutshell, sheet2 looks like
this:
Status Item
Open 7
Open 9
Closed 11
Open 10
Open 4

Sheet1 has a table full of "Items".
7 9 11
9 11
4 10 11 7
9 11 9
10 7 9 11

If I go to sheet2 and change the status of Item7 to Closed, I want to go
to
Sheet1, and find all instances of 7 and clearcontents of cell.

Thanks again.


"Bob Phillips" wrote in message
...
Not too clear on what you need, but here is some change event code as
an
example

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Good afternoon. On Sheet2 of my workbook, I have a table that drives
a
data
validation list on sheet1. Sheet2 Col A has the status of Open or
Closed.
Col B has the validation item list. So on sheet1, the list is only
populated with the Open items.

What I am trying to do is this - if the status of an item on sheet2
is
changed to Closed (and therefore removed from the validation list),
how
can
I find that item on Sheet1 and delete it in every cell that it occurs
in?
I
have heard of a worksheet change event, but not familiar with coding
it.
Thank you in advance.

Pleasure

Bob


"Steph" wrote in message
...
Amazing! Thank you!!

"Bob Phillips" wrote in message
...
Hi Steph,

Hope that this does it.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

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("Sheet1")
For Each cell In .UsedRange
If cell.Value = val Then
cell.Value = ""
End If
Next cell
End With

End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Thanks Bob. Wasn't too clear, was I? In a nutshell, sheet2 looks
like
this:
Status Item
Open 7
Open 9
Closed 11
Open 10
Open 4

Sheet1 has a table full of "Items".
7 9 11
9 11
4 10 11 7
9 11 9
10 7 9 11

If I go to sheet2 and change the status of Item7 to Closed, I want to
go
to
Sheet1, and find all instances of 7 and clearcontents of cell.

Thanks again.


"Bob Phillips" wrote in message
...
Not too clear on what you need, but here is some change event code
as
an
example

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
Good afternoon. On Sheet2 of my workbook, I have a table that
drives
a
data
validation list on sheet1. Sheet2 Col A has the status of Open or
Closed.
Col B has the validation item list. So on sheet1, the list is only
populated with the Open items.

What I am trying to do is this - if the status of an item on sheet2
is
changed to Closed (and therefore removed from the validation list),
how
can
I find that item on Sheet1 and delete it in every cell that it
occurs
in?
I
have heard of a worksheet change event, but not familiar with
coding
it.
Thank you in advance.