Two Cell Change Worksheet Macro - Private Sub Worksheet_Change(ByVal Target As Range)

Srivatsal

New Member
Joined
Dec 4, 2017
Messages
7
Hi,

Need help I am new pretty new to VBA, created a worksheet it is working for 1 condition but I tried a lot but unable to make it work for the second condition in the same worksheet.

Background: The first code pasted below calls the I need to below code to call a macro when the condition1 cell changes to "Initaited" similarly I want it to call second macro in general module when the condition1 cell changes to "Completed"

PLEASE HELP

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("N" & Rows.Count).End(xlUp).Row
'This assumes a Header Row in N1
If Not Intersect(Target, Range("N2:N" & LR)) Is Nothing Then
' If Target.Value = "Initiated" Then
Call Create_Mail_From_List(Target)
' End If
End If
End Sub

Name
Email address
Condition1
Condition2
User1
1@c.com
Initiated
Completed
User1
2@c.com
Initiated
Completed
User1
3@c.com
Initiated
Completed

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,024
Office Version
  1. 2013
Platform
  1. Windows
Maybe this is what you want:
Modify to your needs. Or explain more.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("N" & Rows.Count).End(xlUp).Row
'This assumes a Header Row in N1
If Not Intersect(Target, Range("N2:N" & LR)) Is Nothing Then
If Target.Value = "Initiated" Then Call Bob
If Target.Value = "Completed" Then Call George
End If
End If
End Sub
 

Srivatsal

New Member
Joined
Dec 4, 2017
Messages
7
Maybe this is what you want:
Modify to your needs. Or explain more.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long
LR = Range("N" & Rows.Count).End(xlUp).Row
'This assumes a Header Row in N1
If Not Intersect(Target, Range("N2:N" & LR)) Is Nothing Then
If Target.Value = "Initiated" Then Call Bob
If Target.Value = "Completed" Then Call George
End If
End If
End Sub

Tried it but does not work Unfortunatley... If anybody could help me on this please.
 

Srivatsal

New Member
Joined
Dec 4, 2017
Messages
7
NameEmail addressCondition1Condition2
User11@c.comInitiatedCompleted
User12@c.comInitiatedCompleted
User13@c.comInitiatedCompleted


<tbody>
</tbody>
Lets assume the colums are "N" for condition1 and column "S" for condition2 in the above case. Please help guys!!
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,024
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Try this:

Modify calling Macro name.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Or Target.Column = 19 Then
If Target.Column = 14 And Target.Value = "Initiated" Then Call Bob
If Target.Column = 19 And Target.Value = "Completed" Then Call George
End If
End Sub
 

Srivatsal

New Member
Joined
Dec 4, 2017
Messages
7
Try this:

Modify calling Macro name.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 14 Or Target.Column = 19 Then
If Target.Column = 14 And Target.Value = "Initiated" Then Call Bob
If Target.Column = 19 And Target.Value = "Completed" Then Call George
End If
End Sub

This is also not working - Its giving a compile error on "Exit Sub" and highlighting yellow on the first line "Private Sub Worksheet_Change(ByVal Target As Range)"

[IMG]C:\Users\lakshmis\Desktop\Macro[/IMG]
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,024
Office Version
  1. 2013
Platform
  1. Windows
If I understand you correctly you want:

If you enter "Initiated" in column "N" you want to run a macro
If you enter "Completed" in column "S" you want to run a macro

So I have call Bob
Call George

I do not know the names of the Macros you want to run so you must change George and Bob to the Macro name you want to run

If this is not what you want then you need to explain more
 

Watch MrExcel Video

Forum statistics

Threads
1,114,518
Messages
5,548,521
Members
410,844
Latest member
Juno49
Top