Running a Macro when cell value is a specified value

[MichaelRobert]

I want to fire my macro when cell "b3' has the value of "#N/A". I have tested
my macro and it runs fine with b3 = #N/A; but I can only get it to run if I
run the macro manually. I want it to run whenever b3 = #N/A. The macro
performs to change the value of b3 to a non-error value.

How can I get the macro to fire when b3 = "#N/A"?

Thanks for your help.

 

[JE McGimpsey]

One way:

Run it as an event macro. Assuming B3 is the result of a calculation,
put this in your worksheet code module (right-click the worksheet tab
and choose View Code):

Private Sub Worksheet_Calculate()
With Me.Range("B3")
If IsError(.Value) Then _
If .Value = CVErr(xlErrNA) Then MyMacro
End With
End Sub

 

[Gary''s Student]

Insert this event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B2").Text = "#N/A" Then
MsgBox ("Hi Mike")
End If
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[MichaelRobert]

Thanks to you both. I had not thought about making it an event. It works
well. Thanks again.

Mike