Multiple worksheet change events in one sheet

[vekdas]

Hi

Can anyone help - I am trying to create two worksheet changes which need to happen as a result of a change in a cell. There are two sets of code below which I believe I need to try and combine but I am not sure how??

The code for the first change is: (this works fine on its own - it hides rows wherever the word 'delete' is in a specific cell within that row (within a range called "Trusts2" , otherwise it does not hide the row.
This code has to run through a number of rows to decide whether to hide or show the row.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim xRg As Range

Application.ScreenUpdating = False

For Each xRg In Range("Trusts2")
If xRg.Value = "Delete" Then
xRg.EntireRow.Hidden = True

Else

xRg.EntireRow.Hidden = False

End If

Next xRg

Application.ScreenUpdating = True

End Sub


The second code I want to add to this should update all cells in a known range to "Yes" on the same sheet. If I was just writing a simple macro for it, it would look like this:

Sub Reset_Comparison_Trusts()

Sheet15.Range("YesorNo2").Value = "Yes"

End Sub

Many thanks for any help in showing how best to combine these!

 

[vekdas]

Hi Kev,

from looking at your workbook I think it will be good enough to just monitor changes to C3 to trigger the code: change the contents in Range YesorNo2 to Yes, make all rows for the range Trusts2 and loop through Range Trusts2 to hide all rows where Delete is found. I still try to find out what you mean by



The code so far as described above:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngCell As Range

'allow only one cell to be changed to start the processing
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Not Intersect(Target, Range("C3")) Is Nothing Then
  Application.EnableEvents = False
  Range("YesorNo2").Value = "Yes"
  Range("Trusts2").EntireRow.Hidden = False
  Application.EnableEvents = True
  For Each rngCell In Range("Trusts2")
    rngCell.EntireRow.Hidden = (rngCell.Value = "Delete")
  Next rngCell
End If

Application.ScreenUpdating = True

End Sub

As you can see from my profile I can run the code but will get just errors as I'm still using 2019. So I kept the data by using a different cell to trigger.

Ciao,
Holger

Hi Holger

This code works perfectly - thank you so much for taking the time to help!

Best Wishes

Kev

 

[HaHoBe]

Hi Kev,

thanks for the feedback, glad to be of help.

Holger