Worksheet_Change - NEW to VBA
 

Another user kindly provided me with the following Worksheet_Change
macro.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$F$454" Then
Select Case Target.Value
Case "Growth Rate"
Range("H454:q454").NumberFormat = "0.0%"
Case "Value"
Range("H454:q454").NumberFormat = "#.#"
End Select
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

I would like to use relative references, so that if I insert a row
above above F454 the Worksheet_Change macro keeps working. Can someone
help me to modify the macro?

What would be a good starting point for someone interested in applying
VBA to finance?

Thanks a lot.

PiPPo

Worksheet_Change - NEW to VBA
 

Give cell F454 a name (InsertNameDefine...), range H454:Q454. and then use
the names with

If Target.Count = 1 And Not Intersect(Target,Range("the_name")) IS
Nothing then

and


Range("other_name").NumberFormat = "0.0%"
Case "Value"
Range("other_name").NumberFormat = "#.#"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

wrote in message
ups.com...
Another user kindly provided me with the following Worksheet_Change
macro.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$F$454" Then
Select Case Target.Value
Case "Growth Rate"
Range("H454:q454").NumberFormat = "0.0%"
Case "Value"
Range("H454:q454").NumberFormat = "#.#"
End Select
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

I would like to use relative references, so that if I insert a row
above above F454 the Worksheet_Change macro keeps working. Can someone
help me to modify the macro?

What would be a good starting point for someone interested in applying
VBA to finance?

Thanks a lot.

PiPPo