How to run a macro based on cell value - Revisited

[Haleigh]

A few days ago Frank Kabel and Paul Lautmen kindly advised
on how to run a macro based on the value of a cell. Their
advice to use the Worksheet_Change event works great.
However, just as it's name implies, the code runs every
single time anything on the worksheet changes, whether it
applies or not. Wonderful as it is, it isn't really
efficient in a large spreadsheet and is not really what I
was hoping for. (Not that I'm hard to please, mind you!)

Ideally, what I'd like is for the macro to run as soon as
you exit the cell (similar to MS Access "On Exit" event.
This way, the macro runs only when it's required to.

Does anyone else have some ideas on how to run a macro
based on the value of a cell?

Thank you,
Haleigh

 

[Jonathan Rynd]

However, just as it's name implies, the code runs every
single time anything on the worksheet changes, whether it
applies or not.

The parameter to worksheet_change is the cell which is being changed. If
you examine the address of this range, and check whether it is the right
one, you can choose whether to run the macro.

 

[jpendegraft]

Two (of the many possible approaches)

The CHANGE event can be isolated to handle any changes within a range.
Example: Asssuming you have a named range called INPUTRANGE.

Private Sub Worksheet_Change (ByVal Target As Excel.Range)
Dim VRange As Range
Set VRange = Range("InputRange")
If Not Intersect (Target, VRange) Is Nothing Then_
MsgBox "A changed cell is in the input range."
End Sub

Your event would go in place of the msgbox line....

The second way would be to evalute the value of each cell. You mak
reference to this in your last line....this can be done based on man
of the loops or decision making arguments...

Cheers

 

[Haleigh]

Thank you, jpendegraft and Jonathan, for your assistance!