Worksheet_Change to target two specific cells

[Sandcastor]

I require a little help streamlining a Worksheet_Change event to target only two cells.

In info sheet requires my technicians entering the date their meters were last calibrated, and pops up a warning dialogue (which works just fine) when the meters are due to be calibrated, or overdue.
The Script:
Based on a date entered into named range "CalDate1" and "CalDate2", two other reference Cells (CA10 and CB10 respectively) return the values "Good", "Due", and "Overdue". I have the warning dialogue scripts called on a Worksheet_change, but this causes every entry in the sheet take nearly a whole second to update.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("CA10").Value <> 0 Then
        Serial1
    End If
    
    If Range("CB10").Value <> 0 Then
        Serial2
    End If

End Sub

Can I focus the worksheet_change on the two ranges where the date is entered, so that the script fires only when a date is entered into either "CalDate1" or "CalDate2"?

 

[oldrelia23]

I require a little help streamlining a Worksheet_Change event to target only two cells.

In info sheet requires my technicians entering the date their meters were last calibrated, and pops up a warning dialogue (which works just fine) when the meters are due to be calibrated, or overdue.
The Script:
Based on a date entered into named range "CalDate1" and "CalDate2", two other reference Cells (CA10 and CB10 respectively) return the values "Good", "Due", and "Overdue". I have the warning dialogue scripts called on a Worksheet_change, but this causes every entry in the sheet take nearly a whole second to update.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("CA10").Value <> 0 Then
        Serial1
    End If
    
    If Range("CB10").Value <> 0 Then
        Serial2
    End If

End Sub

Can I focus the worksheet_change on the two ranges where the date is entered, so that the script fires only when a date is entered into either "CalDate1" or "CalDate2"?

Try wrapping your code with:

If Not Intersect(Target, Range("CalDate1", "CalDate2")) Is Nothing Then

'Insert original code

End if

 

[Sandcastor]

Cool, I can't wait to try it tomorrow at work. For the sake of learning, can you plain-language the container for me?
What exactly do the IF NOT INTERSECT, and IS NOTHING do?

 

[Sandcastor]

If not intersect... Seems to tell worksheet_change to say if the changes are not within target range (each cell intersecting with only itself = 1 cell per instance= only my 2 cells), then IS NOTHING tells script to ignore completely. Am I on the right track here?

 

[oldrelia23]

You're definitely on the right track. The target is the range of the cell that was changed. If that cell and the range spanned by your two named ranges have cell in common (there is some intersection), then it will run the code. The not/nothing is similar to an "at least one" concept; as long as the intersection is not null then it will run the code. Hope it helps.

 

[Sandcastor]

Absolutely. I believe the learning from the board is just as important as getting useful code out of it. I have only 35 posts because I scour for solutions first, and cobble together an understanding of concepts to use. This one had me stumped, too many new terms all at once. Thanks for a slice of understanding! Again, I'll post tomorrow how it all went.

 

[taurean]

One more option will be like this:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target

Case Range("Caldate1")
    Serial1

Case Range("Caldate2")
    Serial2

End Select

End Sub