active cells in range
If any cell (with value 0) in the range L2:T10 is selected I wish to update
the corresponding cell in the range B13:J21 with the value in $P$1. |
active cells in range
have I understood your question???
in B13 type =IF(L2<0,$P$1,"") copy B13 down and right upto J21 see whether you get what you want ============================ gocats wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
active cells in range
Unfortunately no, as I when i select say L2, have it populated from B13
which is set to $P$1. "R.VENKATARAMAN" wrote: have I understood your question??? in B13 type =IF(L2<0,$P$1,"") copy B13 down and right upto J21 see whether you get what you want ============================ gocats wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
active cells in range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
active cells in range
Thanks Bob
Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
active cells in range
How do I include a second range "Vl2:AD10" in the string and Me.Range("Z1")
and double click to set the active cell to 0? "gocats" wrote: Thanks Bob Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
active cells in range
I don't quite understand what you want to do with Z1, but here is the bit on
a second range and setting to 0 on double-click. Private Const WS_RANGE As String = "L2:T10,V12:AD10" Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error GoTo wb_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Value = 0 End With End If wb_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... How do I include a second range "Vl2:AD10" in the string and Me.Range("Z1") and double click to set the active cell to 0? "gocats" wrote: Thanks Bob Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
active cells in range
Thanks Bob, that works well.
Unfortunately these things grow. I also have macros in the same range that now do not work. i.e. either the VB code works or the macros work. What do I need to do? "Bob Phillips" wrote: I don't quite understand what you want to do with Z1, but here is the bit on a second range and setting to 0 on double-click. Private Const WS_RANGE As String = "L2:T10,V12:AD10" Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error GoTo wb_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Value = 0 End With End If wb_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... How do I include a second range "Vl2:AD10" in the string and Me.Range("Z1") and double click to set the active cell to 0? "gocats" wrote: Thanks Bob Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
active cells in range
Not sure I get the picture.
What macros, and why do they not work? -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... Thanks Bob, that works well. Unfortunately these things grow. I also have macros in the same range that now do not work. i.e. either the VB code works or the macros work. What do I need to do? "Bob Phillips" wrote: I don't quite understand what you want to do with Z1, but here is the bit on a second range and setting to 0 on double-click. Private Const WS_RANGE As String = "L2:T10,V12:AD10" Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) On Error GoTo wb_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target .Value = 0 End With End If wb_exit: Application.EnableEvents = True End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... How do I include a second range "Vl2:AD10" in the string and Me.Range("Z1") and double click to set the active cell to 0? "gocats" wrote: Thanks Bob Absolutly brilliant! "Bob Phillips" wrote: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Const WS_RANGE As String = "L2:T10" On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target If .Value 0 Then .Offset(11, -10).Value = Me.Range("P1").Value End If End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH RP (remove nothere from the email address if mailing direct) "gocats" wrote in message ... If any cell (with value 0) in the range L2:T10 is selected I wish to update the corresponding cell in the range B13:J21 with the value in $P$1. |
All times are GMT +1. The time now is 06:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com