worksheet change event for multiple cells

[andrewb90]

Hello all,

I have this code:

 Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, [H16]) Is Nothing Then
    If [H16] < -9.99 Then
        [I1].EntireColumn.Hidden = False
    Else: [I1].EntireColumn.Hidden = True
    End If
End If
End Sub

It works perfectly, but I have multiple ranges that I need to apply it to, and I've been unsuccessful at adjusting it properly.
H17, H18, H20, H22 all need the same thing applied to it. Also if there is a way to make two conditions so if the cell value is -10 or less or 10 or more, that'd be very helpful as well.

 

[footoo]

Do all four cells need to meet the conditions, or only the cell that is changed?

What if more than one of the cells is changed at the same time and not all of the changed cells meet the conditions (as could happen, for example, with copy/pastespecial/add) ?

 

[andrewb90]

Only the cell that is changed, but for all of those cells, the effect is the same (unhiding column I)

So if multiple cells were say copy & pasted, then as long as one of them triggered it, the column would unhide. Also, unhiding would take priority over hiding, so if multiple cells got entered all at once if any cell required I to be unhidden, then it would stay that way.

 

[pgc01]

H17, H18, H20, H22 all need the same thing applied to it.

You can use your test, just add the other cells, like:

If Not Intersect(Target, [H17,H18,H20,H22]) Is Nothing Then

The rest of your questions depend on your answer to footoo's questions.

 

[footoo]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
Set rng = Intersect(Target, [H16:H18,H20,H22])
If Not rng Is Nothing Then
    For Each cel In rng
        If cel <= -10 Or cel >= 10 Then
            [I1].EntireColumn.Hidden = False
            Exit For
        Else: [I1].EntireColumn.Hidden = True
        End If
    Next
End If
End Sub

 

[andrewb90]

Awesome! that worked perfectly! thank you!