Problem with Target.Range

IronHat

New Member
Joined
May 6, 2015
Messages
12
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Range="$C$3") And (Target.Range=$C$3) Then
    Call Macro1(Target.Address(False, False), Target.Value)
End If
End Sub
I am trying it to detect changes made in cell C3 and D3 and if changes are made in both the cells only then call macro 1.
However And is not working. If (Target.Range=$C$3) And (Target.Range=$D$3) doesnt work. I know it works with OR.
What to do?
 

IronHat

New Member
Joined
May 6, 2015
Messages
12
Try
Code:
If Target.Address = "$C$3" Then

Its still not calling macro 1 on change in both the cells.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$C$3") And (Target.Address = "$D$3") Then
    Call Macro1(Target.Address(False, False), Target.Value)
End If
End Sub
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,726
Office Version
2010
Platform
Windows
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rInt          As Range

  rInt = Intersect(Target, Range("C3:D3"))
  If Not rInt Is Nothing Then
    If rInt.Cells.Count = 2 Then
      Call Macro1(Target.Address(False, False), Target.Value)
    End If
  End If
End Sub
I'm skeptical about the arguments passed to Macro1, though. They would have to be a String and a Variant.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,402
Office Version
365
Platform
Windows
Change And to Or.
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
If you change the value in cell C3 the event fires and target.address is "$C$3", there is nothing there that waits for you to change cell D3. Change a cell the event fires.
If you highlight cell C3 and D3 and enter a value and hit control + enter then both cells will get the same value and the target.address will be "$C$3:$D$3", but highlight them both and entering a value and just normally hitting enter will only result in C3 being the target.
You have to come up with another tactic if you want to check if C3 has been changed from a former value when you change D3 or vice versa.
If this is even what you are after, which to me it sounds like that is what you are after, or maybe I'm reading your original post wrong.
Public variables perhaps that checks the values in the cells when the worksheet opens?
 
Last edited:

IronHat

New Member
Joined
May 6, 2015
Messages
12
Thanks for the reply shg
I am getting an error message at
rInt = Intersect(Target, Range("C3:D3"))
 
Last edited:

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,726
Office Version
2010
Platform
Windows
Sorry,

Code:
[COLOR="#FF0000"]Set [/COLOR]rInt = Intersect(Target, Range("C3:D3"))
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,726
Office Version
2010
Platform
Windows
What are you doing from the user interface to trigger it?
 

Forum statistics

Threads
1,085,842
Messages
5,386,292
Members
401,992
Latest member
CleverHopper

Some videos you may like

This Week's Hot Topics

Top