I suspect that MACRO200 is 'out of scope', meaning that the code in the
worksheet module cannot see Macro200 where ever it is.

But you can pin it down more and be su when the error occurs, choose the
[Debug] option and see what line of code is highlighted - that's the line of
code with the error in it. If it is as I suspect, then there's a simple fix
- cut and paste the code from Macro200, all the way from it's Sub Macro200()
definition to End Sub from where ever it is at and paste it into the
Worksheet code module below the end of the _Change event End Sub. Although
this might cause a problem if the code in Macro200 is referencing anything
else on another worksheet or workbook. Then you have to specifically direct
those references to them.

As I said earlier, if this is the only place that is using Macro200, you
could even replace the Call Macro200 statement with the code within it.

Also, the Option Explicit statement forces a requirement to declare all
variables before they are used via a Dim statement. Macro200 is not a
variable, should be the name of the macro you're calling, so it's 'exempt'
from that rule. But you may have undeclared variables in the Macro itself?

If you want me to take a look at it this evening, email the .xls file as an
attachment to jlatham @ jlathamsite.com (no spaces).

"Steve" wrote:

This seems like it should really work, however, when I change anything in C1,
I get a "variable not defined" error. What am I doing wrong?

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), C1)
If iSect Is Nothing Then
Exit Sub
End If
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub

"JLatham" wrote:

I think the Worksheet's _Change event is where you probably need to go. This
code will check each time a change is made on the worksheet, but won't do
anything unless a change was made in cells C1 or D1. It then looks at what's
in the cell that was changed and if it matches a specified contractor name,
it will call Macro22. You could probably include the code from Macro22 in
line at this point if you want; if not, make sure that Macro22 is not a
Private Sub Macro() declaration.

If you've got a long list of contractors to examine, there are ways to deal
with that in a more compact way than with multiple IF statements or long ones
using lots of OR statements within it.

Hope this gives you some ideas.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iSect As Range

iSect = Application.Intersect(Range(Target.Address), "C1:D1")
If iSect Is Nothing Then
Exit Sub
End If
'here if changed cell was C1 or D1
'UCase converts to all uppercase
'Trim gets rid of leading/trailing white space
'helping guarantee that comparison is as
'valid as we can make it
If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
'Macro22 cannot be 'Private'
Macro22 ' could use Call Macro22
End If

End Sub



"Steve" wrote:

I need to call a macro if a cell equals a contractors name.
Something like:
if c1:d1 = "Integrity Remodeling" then
call macro22
end if
end sub
But I cant get it work. Any suggestions