Only want macro to fire when i change certain cells, not any cell

[graduate106]

I'm a bit of a newby when it comes to macros so excuse my ignorance!

I have the following macro which i found online somewhere which basically hides and shows rows/columns based on whether a box has a 0 in it. The problem is, the macro fires every time a cell is changed on the worksheet which is getting a bit tedious. I only want it to fire when say cells XX to XX are changed.

Im sure this will be easy amended to allow this - can someone help please?

Many thanks in advance

Here is my current macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False

Dim ACell As Range

For Each ACell In Range("j10:j20")
If ACell.Value = 0 Then
ACell.EntireRow.Hidden = True
Else
ACell.EntireRow.Hidden = False
End If
Next ACell

Application.ScreenUpdating = True
End Sub

 

[Joe4]

Use INTERSECT to see if the cell being changed is in your range of defined cells. Here is an example of how to do that: http://www.mrexcel.com/forum/showthread.php?t=392070

 

[graduate106]

Thanks for the response but sorry im confused with the link.

What would my macro need to be to only fire the macro when boxes G30:G31 are changed? (as opposed to firing it every time any cell is changed on the whole worksheet which it is doing now)

Thanks

 

[Joe4]

You cannot stop it from firing every time any cell changes on a worksheet. What you can do it have the first step exit the macro if the change didn't occur in the range that you are looking for. That is what the INTERSECT method is used for. You are seeing if the cell that the change occurred in (TARGET) resides in your designated range. If so, proceed with the macro, else exit.

 

[graduate106]

Ah ok i see, so what would my macro need to be then to add this intersect function to cells g30:g31? (sorry im a total novice on macros)

Thanks

 

[Joe4]

This line will exit the macro if the change is not occuring in G30:G31.
So place it at the beginning of the code.

If Intersect(Target, Range("G30:G31")) Is Nothing Then Exit Sub

 

[graduate106]

GENIUS! - thank you so much!