Need cell address

**Ferloft** · 06-28-2013, 08:09 AM

Hello,

I am using the following code to call a macro whenever one of the key cells is changed

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range, a As Range
Set KeyCells = Sheets(1).Range("$A$55,$B$55,$C$11:$C$18,$C$29,$C$42,$C$46,$C$55,$D$35")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then Call Macro1
Set a = Range(Target.Address)
End Sub

I would like a to be the cell address of the cell that changed. I then need to know how to use the variable a within my macro.

If that doesnt make sense I can try to explain more.

Thanks.

**HaHoBe** · 06-28-2013, 08:22 AM

Hi, Ferloft,

what´s Macro1 purpose as it is called prior to setting a? Usually you refer to the cells from the sheet the event is called from:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range, a As Range
Set KeyCells = Range("$A$55,$B$55,$C$11:$C$18,$C$29,$C$42,$C$46,$C$55,$D$35")
If Not Intersect(KeyCells, Target) Is Nothing Then Call Macro1
Set a = Target
If Not a Is Nothing Then Debug.Print a.Address
End Sub

Ciao,
Holger

**patel45** · 06-28-2013, 08:23 AM

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range, a As Range
Set KeyCells = Sheets(1).Range("$A$55,$B$55,$C$11:$C$18,$C$29,$C$42,$C$46,$C$55,$D$35")
If Not Application.Intersect(KeyCells, Target)) Is Nothing Then Call Macro1
Set a = Target
End Sub

**Ferloft** · 06-28-2013, 08:25 AM

I just added a in there I have no idea if thats where it should actually go. Essentially whenever one of the cells in the KeyCells range is changed I want Macro1 to run. However at the end of Macro1 I would like the last step to be select the cell below whichever cell was changed.

patel: It didn't like me taking out the .Address part of finding the intersect. When I put it back in it still didn't set a to the cell that changed.

**HaHoBe** · 06-28-2013, 08:31 AM

Hi, Ferloft,

maybe like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("$A$55,$B$55,$C$11:$C$18,$C$29,$C$42,$C$46,$C$55,$D$35")
If Not Intersect(KeyCells, Target) Is Nothing Then Call Macro1
Application.EnableEvents = False
Target.Offset(1, 0).Select
Application.EnableEvents = True
End Sub

Ciao,
Holger

**Ferloft** · 06-28-2013, 08:35 AM

That one seems to be working Holger, thanks! I will mark the thread as solved and if I run into any problems I'll make sure to come back.

Just out of curiosity, why do you turn off events while selecting the cell?

**HaHoBe** · 06-28-2013, 08:44 AM

Hi, Ferloft,

a habit of mine from the days of using Excel2000 (it could lead to endless loops there - better excuse than stating I just forgot what event I was handling

): this will not get the event fired in case any cursor movement (SelectionChange) or action (Change) has taken place. May be omitted here (both of the lines).

Ciao,
Holger

**nilem** · 06-28-2013, 08:31 AM

Hi Ferloft
I try to guess

maybe something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Sheets(1).Range("$A$55,$B$55,$C$11:$C$18,$C$29,$C$42,$C$46,$C$55,$D$35"), Target) Is Nothing Then Exit Sub
Call Macro1(Target)
End Sub

Sub Macro1(r As Range)
'your code here
End Sub