Hide rows based on cell value, continuously

[zakavelli]

Hi my VBA knowledge is very limited. I am trying to get a macro to hide columns based on a cell value. The cell value is inputted manually and based on this the rows should hide. Basically if the cell d3 is equal to zero then all rows show, but if the value in d3 is greater than 0 then the rows 25:75 are hidden.

I have managed to put together some code based on googling around, and using this forum, and just playing around with code, and luckily for me I do not get any errors.

I've put the VBA into the VBA window by right clicking the sheet tab, and clicking view source using General and Declarations. The code I have used is shown below

Private Sub Worksheet_Activate()
If Cells(3, 4).Value > 0 Then
  ActiveSheet.Rows("25:75").Hidden = True
ElseIf Cells(3, 4).Value = 0 Then
  ActiveSheet.Rows("25:75").Hidden = False
End If
End Sub
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells = "$D$10" > 0 Then
    ActiveSheet.Rows("25:75").Hidden = True
ElseIf Target.Cells = "$D$10" = 0 Then
  ActiveSheet.Rows("25:75").Hidden = False
End If
End Sub

And I've saved a macro called 'run' in the macro VBA editor window

with the following code:

Sub run()
If Cells(3, 4) > 0 Then
  ActiveSheet.Rows("25:75").Hidden = True
ElseIf Cells(3, 4) = 0 Then
  ActiveSheet.Rows("25:75").Hidden = False
End If
End Sub

I want the macro to run in the background continuously. Now what happens is if I change the value in d3 from 0 to say '3' nothing happens, where it should hide row 25:75. However I can run the macro manually and it works. but when I then change the value from '3' back to 0 it automatically shows the row 25:75, which it should.

Where it works automatically one way, it doesn't work the other way.


Please can you help?

Thanks,

Zak

 

[ChrisDaniels]

A minor change gets it working..

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$3" Then
        If Target.Value > 0 Then
            ActiveSheet.Rows("25:75").Hidden = True
        Else
            ActiveSheet.Rows("25:75").Hidden = False
        End If
    End If
End Sub

 

[zakavelli]

You legend!

Works perfectly. Thank you.