Hide row based on value (different sheets)

[Challebjoern]

Hey guys.



Been trying to write a formula (vba) to hide rows on sheet2 based on value in sheet1.
This is beacuse I use sheet1 to put in the data to make my business proposal which is shown in sheet2.

And since I don't always use all kind, it doesn't have to show the once who isn't offerd.

Sub HideRow()
    If Range("Blad1!E12").Value = 0 Then
        Rows("Blad2!10").EntireRow.Hidden = True
    Else
        Rows("Blad2!10").EntireRow.Hidden = False
    End If
End Sub

Tried using the one above but doesn't work. Also attached the workbook if you wanna take a look at it. Also, how would you write it to be hide more rows.

Hope you can crack this one for me!

[NoSparks]

Remove ALL the code you have and try putting this into the Blad1 module

Private Sub Worksheet_Change(ByVal Target As Range)

'limit cells to monitor
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E12:E17")) Is Nothing Then Exit Sub

Call HideRow

End Sub

Sub HideRow()

If Sheets("Kalkyl").Range("E12").Value = 0 Then
    Sheets("Färdig Offert-Vy").Rows("10").EntireRow.Hidden = True
Else
    Sheets("Färdig Offert-Vy").Rows("10").EntireRow.Hidden = False
End If

If Sheets("Kalkyl").Range("E13").Value = 0 Then
    Sheets("Färdig Offert-Vy").Rows("11").EntireRow.Hidden = True
Else
    Sheets("Färdig Offert-Vy").Rows("11").EntireRow.Hidden = False
End If

'etc to E17

End Sub

[Challebjoern]

Thanks alot!

It worked like a charm. 5 stars!
Also wrote the rest to make it work on all cells I want too.
___

Originally Posted by NoSparks

Remove ALL the code you have and try putting this into the Blad1 module

Private Sub Worksheet_Change(ByVal Target As Range)

'limit cells to monitor
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("E12:E17")) Is Nothing Then Exit Sub

Call HideRow

End Sub

Sub HideRow()

If Sheets("Kalkyl").Range("E12").Value = 0 Then
    Sheets("Färdig Offert-Vy").Rows("10").EntireRow.Hidden = True
Else
    Sheets("Färdig Offert-Vy").Rows("10").EntireRow.Hidden = False
End If

If Sheets("Kalkyl").Range("E13").Value = 0 Then
    Sheets("Färdig Offert-Vy").Rows("11").EntireRow.Hidden = True
Else
    Sheets("Färdig Offert-Vy").Rows("11").EntireRow.Hidden = False
End If

'etc to E17

End Sub