The attached workbook realizes this concept. However, the solution offered is by no means limited to such a simple calculation. The code has been designed to keep the two calculations, resulting from the user's choice of which cell to enter his data, distinctly separate from each other. Therefore the system is capable of handling completely different calculations. For example, if applied to a data retrieval task the user might look for an item by either entering its price in one cell or its color in another. The resulting look-ups aren't in any way related by a common exchange rate, and that is the advantage of the attached code which handles the two tasks separately.
The code comes in two parts. Drag or copy/paste the module Convert to your own project. Then paste this code to the worksheet's code module where your two cells are.
- Code: Select all
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
With Application
Set Rng = .Union(Range(Cone), Range(Ctwo))
If Not .Intersect(Rng, Target) Is Nothing Then
Convert Target, Rng ', Conv
End If
End With
End Sub
At the top of the code in the module Convert there are two constants defining the two inter-related cells.
- Code: Select all
Public Const Cone As String = "C6"
Public Const Ctwo As String = "D6"