Run macro if any cell in a non-contiguous range are changed

Question

i have a non-contiguous range and i need to run a privatesub, if any of this cells change, but only if all cells in this range aren't empty i've tried this but isn't working, since it's running the macro iven if the cells are empty, here:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D3")) Is Nothing Then

    If (ActiveSheet.Range("D3") <> """") And (ActiveSheet.Range("D5") <> """") And _
       (ActiveSheet.Range("I3") <> """") And (ActiveSheet.Range("O3") <> """") And _
       (ActiveSheet.Range("O5") <> """") And (ActiveSheet.Range("O7") <> """") And _
       (ActiveSheet.Range("X3") <> """") And (ActiveSheet.Range("X5") <> """") _
    Then

    Create

    End If
End If
End Sub

Thank you for your time =]

score 0 · Accepted Answer · answered Feb 19 '15 at 18:09

I think this should meet your requirements:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Range("D3,D5,I3,O3,O5,O7,X3,X5")
    If Intersect(Target, rng) Is Nothing Then Exit Sub
    For Each r In rng
        If r.Value = "" Then
            Exit Sub
        End If
    Next r
    MsgBox "I am going to do something"
End Sub

Run macro if any cell in a non-contiguous range are changed

1 Answers1