Conditional hiding of rows based on value in a linked cell

[K. Georgiadis]

The following code by XLD works perfectly, as long as the value is typed in cell G17 and entered with the Return key:


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$17" Then

Range("17:19,54:54").EntireRow.Hidden = Target.Value = 0
End If

End Sub


The code does not work if cell G17 contains the formula:

IF(G11>F11,M5,0)

making its value dependent on cell M5. Cell G17 shows the correct values but the designated rows do not hide/unhide as desired.

What modifications are necessary for the conditional hiing/unhiding to take place?

Thanks in advance!

[Charlize]

I've tried this one.
Private Sub Worksheet_Change(ByVal Target As Range)
'If value in G17 = 0 hide de rows
If Cells(7, 17).Value = 0 Then
Range("17:19,54:54").EntireRow.Hidden = Target.Value = 0
End If
End Sub
Charlize

[K. Georgiadis]

Unfortunately, it does not work for me. To explain the worksheet logic:

Cell G17 contains an incentive payment of X$ per pound if 2007 sales exceed 2006 sales. However, the user wants the flexibility of not paying the premium, even if 2007 sales exceed 2006. Off to the right, I added a drop down box -- with linked cell M5 -- whereby the user can select 0 or $3 (the incentive rate) so that 0 can be inserted in cell G17 even if the incentive criteria are met. I get the right numbers in G17 but I cannot get the designated rows to hide/unhide.

[Bob Phillips]

Private Sub Worksheet_Calculate()
Range("17:19,54:54").EntireRow.Hidden = Range("G17").Value = 0
End Sub

[K. Georgiadis]

This seems to work flawlessly, and it is so elegantly simple. Thanks!

[Charlize]

And this one ?
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("G17").Value = 0 Then
Range("17:19,54:54").EntireRow.Hidden = True
Else
Range("17:19,54:54").EntireRow.Hidden = False
End If
End Sub

[K. Georgiadis]

It does not work, Charlize. I tried something similar myself and it did not work either. I'm curious to learn why your code doesn't work whereas XLD's simple 3-line code works perfectly.

[Bob Phillips]

It's the event being used.

It's also one line of code, the other two are procedure declarations :-)

[Norie]

The worksheet change event is not fired when the value of a cell is changed as a result of a formula.

[Charlize]

tried the solution of xld and mine. Xld's in calculate and mine in change.
comment either one out or. Maybe i'm not seeing well but it seems to me they are doing the same. Hide if G17 = 0. Change M5 with dropdownbox to 0 will hide rows.

Charlize

or am i missing something.