want to apply multiple Private Sub Worksheet_Change(ByVal Target As Range)?

[aashish83]

I need to run the below multiple times (24 times) to be precise in a single sheet- Basically there is answer box in C column (starting from C19) after every 4 rows and i want to hide the in between rows if the answer column C particular Cell is "Yes"

Sub MacroCF1()
Range("A20:A23").EntireRow.Hidden = False
End Sub
Sub MacroCF2()
Range("A20:A23").EntireRow.Hidden = True
End Sub

Private Sub Worksheet_Changes(ByVal Target As Range)

If Not Intersect(Target, Range("C19")) Is Nothing Then
Select Case Target.Value
Case "Yes"
Call MacroCF2
Case "No"
Call MacroCF1

End Select
End If
End Sub

 

[dmt32]

You can only have one of each available event in each worksheet & you must not rename them as shown below.

Private Sub Worksheet_Changes(ByVal Target As Range)

try this update to your code & see if does what you want

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("C19,C24,C29,C34,C39,C44,C49,C54,C59,C64,C69,C74,C79," & _
                                   "C84,C89,C94,C99,C104,C109,C114,C119,C124,C129,C134")) Is Nothing Then

        Target.Offset(1).Resize(4).EntireRow.Hidden = UCase(Target.Value) = "YES"
    End If
End Sub

Code assumes values in Column C are changed either directly or by code (not formula)

Hope I have a the cell addresses correct but update if required

Dave

 

[aashish83]

You can only have one of each available event in each worksheet & you must not rename them as shown below.

Private Sub Worksheet_Changes(ByVal Target As Range)

try this update to your code & see if does what you want

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Me.Range("C19,C24,C29,C34,C39,C44,C49,C54,C59,C64,C69,C74,C79," & _
                                   "C84,C89,C94,C99,C104,C109,C114,C119,C124,C129,C134")) Is Nothing Then

        Target.Offset(1).Resize(4).EntireRow.Hidden = UCase(Target.Value) = "YES"
    End If
End Sub

Code assumes values in Column C are changed either directly or by code (not formula)

Hope I have a the cell addresses correct but update if required

Dave

Worked like a Charm!!!! Thanks Dave

 

[dmt32]

Worked like a Charm!!!! Thanks Dave

Most welcome & appreciate your feedback

Dave

 

[aashish83]

Most welcome & appreciate your feedback

Dave

Hi Dave Can i request your help on another query that i have

What i want is what below the below code does but in a new WorkBook and if i have multiple ranges? for e.g. my second range would be B19:B2700 and if that is so it should Copy Sheet FSR Level B instead of FSR Level A and third Range would be C19:C2700 and output to be FSR Level C sheet. Greatly appreciate any help on this

Sub Create_CentralA()
Dim sh1 As Worksheet, sh2 As Worksheet


Set sh1 = Sheets("FSR Level A")


Set sh2 = Sheets("BI")



For Each c In sh2.Range("A35:A2700", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
sh1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = c.Value
Next

End Sub

 

[dmt32]

Its a different question & should be in a new thread where others here can offer assistance.

Dave

 

[aashish83]

Its a different question & should be in a new thread where others here can offer assistance.

Dave

Yes i have already opened a new thread for it