cell change event

[Guest]

I'm having trouble making a subroutine run upon a change to the value in cell
B2. This is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "B2" Then
Dim Blank_count As Integer
etc
etc
End If
End Sub

I'm sure I'm missing something obvious, thank you in anticipation! ... Greg

 

[Guest]

Use absolute reference:

If Target.Address = "$B$2" Then

Stefi

„gvm†ezt írta:

 

[Guest]

Thanks Stefi, I was so close!

A supplementary question if I may: in an attempt to avoid the inflexibility
of using an absolute address, I experimented with referring to a defined name
instead but I could not make it work, ie I tried both Target.Address =
"cars" and Target.Address = cars and neither worked. Is there an alternative
to the absolute address method?
TIA ... Greg

 

[Rowan]

One way:

Dim overlap As Range
Set overlap = Intersect(Target, Range("Cars"))
If Not overlap Is Nothing Then
Dim blank_count As Integer
'etc
'etc
End If