I have two columns in a worksheet, one for £ and one for $.
There maybe a value both columns or in just one of the two.
If there is a valu in each column, I want to leave them alone, but
where there is a value in the $ column but not in the £ column I want
to apply a rate (say 1.7) and vice versa.
So I _dont__want a function in column b to say =A1*1.7 as that will be
applied to all values.
The range that the code should be applied to is A3:end of columns and
B3:end of columns (to allow for headings).
Is it possible to do this in code??

The Function works but changes some values that were set at different
rates, and I dont want these changed.
Struggling a bit here, so any assistance would be great!


--
QuickLearner
------------------------------------------------------------------------
QuickLearner's Profile: http://www.excelforum.com/member.php...o&userid=35483
View this thread: http://www.excelforum.com/showthread...hreadid=559830

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:B"
Const XRATE As Double = 1.7

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Column = 1 Then
If .Offset(0, 1).Value = "" Then
.Offset(0, 1).Value = .Value * XRATE
End If
Else
If .Offset(0, -1).Value = "" Then
.Offset(0, -1).Value = .Value / XRATE
End If
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

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"QuickLearner"
wrote in message
news:QuickLearner.2aq599_1152526153.4345@excelforu m-nospam.com...

I have two columns in a worksheet, one for £ and one for $.
There maybe a value both columns or in just one of the two.
If there is a valu in each column, I want to leave them alone, but
where there is a value in the $ column but not in the £ column I want
to apply a rate (say 1.7) and vice versa.
So I _dont__want a function in column b to say =A1*1.7 as that will be
applied to all values.
The range that the code should be applied to is A3:end of columns and
B3:end of columns (to allow for headings).
Is it possible to do this in code??

The Function works but changes some values that were set at different
rates, and I dont want these changed.
Struggling a bit here, so any assistance would be great!


--
QuickLearner
------------------------------------------------------------------------
QuickLearner's Profile:
http://www.excelforum.com/member.php...o&userid=35483
View this thread: http://www.excelforum.com/showthread...hreadid=559830