How about:

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

if target.cells.count 1 then exit sub

Set iSect = Nothing
On Error Resume Next
Set iSect = Application.Intersect(Target, Me.Range("C1"))
On Error GoTo 0

If iSect Is Nothing Then
Exit Sub
End If

If UCase(Trim(Target.Value)) = "INTEGRITY REMODELING" Then
Call MACRO200
End If

End Sub



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

--

Dave Peterson