macro call
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
|