I have generated the script below that will take three differenct currency
exchange rates located in Cells D1:F1 and based on a target cell input will
then convert the remaining adjoining cells into the adjusted currencys. The
script works well and the target cell when inputted the text turns red while
the adjoining cells text is black.

My problem is that if I change any of the currency rates in Cells D1:F1,
none of the target cells update. I auapect I need to enter some script that
will allow updating but I am at a loss as to what the script should be.
Obviously, it is not a recalc as there are no formula in the range cells.

Can someone please assist here? Thank you for any help offered.

Frick

Example:

D1:F1 1.00 0.81 1.71

Target Cells D9:F1500 1.23 1.00 2.11 (where the input
cell was E9 @1.00)


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sENTRYRANGE As String = "D9:F1500,G9:I1500,J9:L1500"
Const sRATERANGE As String = "D1:F1"
Dim rateArr As Variant
Dim entryArr As Variant
Dim rArea As Range
Dim temp As Double
Dim nCol As Integer
Dim startCol As Integer
Dim i As Integer

With Target
If .Count 1 Then Exit Sub
If Not Intersect(.Cells, Range(sENTRYRANGE)) _
Is Nothing Then
For Each rArea In Range(sENTRYRANGE).Areas
If Not Intersect(.Cells, rArea) Is Nothing Then
startCol = rArea(1).Column
End If
Next rArea
rateArr = Range(sRATERANGE).Value
ReDim entryArr(1 To 1, 1 To UBound(rateArr, 2))
nCol = .Column - startCol + 1
entryArr(1, nCol) = .Value
temp = entryArr(1, nCol) / rateArr(1, nCol)
For i = 1 To UBound(entryArr, 2)
If i < nCol Then _
entryArr(1, i) = temp * rateArr(1, i)
Next i
Application.EnableEvents = False
With Cells(.Row, startCol).Resize(1, UBound(entryArr, 2))
.Value = entryArr
.Font.ColorIndex = xlColorIndexAutomatic
.Font.Bold = False
End With
With Target
.Font.ColorIndex = 3
.Font.Bold = True
End With
Application.EnableEvents = True
End If
End With
End Sub