Proggy_Fox
01-11-2019, 01:11 PM
Hi,
I want to make changes in cells in columns 23 to 26. When I do a change Excel shall remember the line I make the changes in and depending on the changes hide that row. To make it more concrete: When all the cell-contents in columns 23 to 26 are "yes" the row shall hide. Otherwise the row shall not hide. I already wrote a code and it works good one time, but always when I do the first run the makro stops after that and the makro does not work any longer. So it hides only one row and none more. May anyone help me? :think:
This is my code so far:
Option Explicit
Dim Zeile As Integer ' Zeile = row
Dim Spalte as Integer ' Spalte = column
Dim rC As Range
Dim Antwort As String ' Antwort = answer
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("W3:Z5000"), Range(Target.Address)) Is Nothing Then
Application.EnableEvents = False
For Each rC In Target.Cells
Zeile = rC.Row
Antwort = "Ja" ' Ja = yes
For Spalte = 23 To 26
If Cells(Zeile, Spalte) = "Nein" Then ' Nein = no
Antwort = "Nein"
End If
Next
If Antwort = "Ja" Then
Rows(Zeile).Hidden = True
ElseIf Antwort = "Nein" Then
Rows(Zeile).Hidden = False
End If
Next
End If
End Sub
I want to make changes in cells in columns 23 to 26. When I do a change Excel shall remember the line I make the changes in and depending on the changes hide that row. To make it more concrete: When all the cell-contents in columns 23 to 26 are "yes" the row shall hide. Otherwise the row shall not hide. I already wrote a code and it works good one time, but always when I do the first run the makro stops after that and the makro does not work any longer. So it hides only one row and none more. May anyone help me? :think:
This is my code so far:
Option Explicit
Dim Zeile As Integer ' Zeile = row
Dim Spalte as Integer ' Spalte = column
Dim rC As Range
Dim Antwort As String ' Antwort = answer
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("W3:Z5000"), Range(Target.Address)) Is Nothing Then
Application.EnableEvents = False
For Each rC In Target.Cells
Zeile = rC.Row
Antwort = "Ja" ' Ja = yes
For Spalte = 23 To 26
If Cells(Zeile, Spalte) = "Nein" Then ' Nein = no
Antwort = "Nein"
End If
Next
If Antwort = "Ja" Then
Rows(Zeile).Hidden = True
ElseIf Antwort = "Nein" Then
Rows(Zeile).Hidden = False
End If
Next
End If
End Sub