Worksheet Change Event- Refer to Target Address By Name
 

Hi,

In the sample code below, how can I substitute a Cell Name for the $B$14 or
$C$14?

Thanks,
Jim


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Fault
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B13:X13")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
If Target.Address = "$B$14" Then
Do stuff....
End If
If Target.Address = "$C$14" Then
Do More Stuff..
End If
Application.EnableEvents = True
Fault:
Application.EnableEvents = True
End Sub

Worksheet Change Event- Refer to Target Address By Name
 

if you mean a Named range then just do this

If Target.Address = Range("'NameOfRange").Address The
----- Jm wrote: ----

Hi

In the sample code below, how can I substitute a Cell Name for the $B$14 o
$C$14

Thanks
Ji


Private Sub Worksheet_Change(ByVal Target As Excel.Range
On Error GoTo Faul
Application.EnableEvents = Fals
If Not Application.Intersect(Target, Range("B13:X13")) Is Nothing The
Target(1).Value = UCase(Target(1).Value
End I
If Target.Address = "$B$14" The
Do stuff...
End I
If Target.Address = "$C$14" The
Do More Stuff.
End I
Application.EnableEvents = Tru
Fault
Application.EnableEvents = Tru
End Su

Worksheet Change Event- Refer to Target Address By Name
 

Jim

If Target.Address = Me.Range("NameofB14").Address Then


--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


Jm wrote:
Hi,

In the sample code below, how can I substitute a Cell Name for the $B$14
or $C$14?

Thanks,
Jim


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Fault
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("B13:X13")) Is Nothing
Then Target(1).Value = UCase(Target(1).Value)
End If
If Target.Address = "$B$14" Then
Do stuff....
End If
If Target.Address = "$C$14" Then
Do More Stuff..
End If
Application.EnableEvents = True
Fault:
Application.EnableEvents = True
End Sub