Worksheet_Change to target two specific cells

Sandcastor

Board Regular
Joined
May 14, 2012
Messages
97
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.
Code:
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"?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

oldrelia23

New Member
Joined
Dec 2, 2010
Messages
25
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.
Code:
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

Board Regular
Joined
May 14, 2012
Messages
97
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?
 
Last edited:

Sandcastor

Board Regular
Joined
May 14, 2012
Messages
97
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

New Member
Joined
Dec 2, 2010
Messages
25

ADVERTISEMENT

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

Board Regular
Joined
May 14, 2012
Messages
97
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

Well-known Member
Joined
Jun 17, 2011
Messages
2,179
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
One more option will be like this:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target

Case Range("Caldate1")
    Serial1

Case Range("Caldate2")
    Serial2

End Select

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,313
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top