I'm trying to write some code which changes each values in a range into a corresponding value from two columns. For example if a value in my range is €29.99. It looks at a table and at column 1 if it finds a value equal to it then changes that cell in the range into the value adjacent in column 2, £24.99.

My problem is the code I have written always returns the same value for all cells the macro affects, the lowest value in the column 2(£9.99). So for example even the values which are €59.99, 29.99 etc are being changed to £9.99. I don't know why this is happening? Or how to resolve it?

€ RRP is column 1 and £ RRP is column 2

[![enter image description here](https://i.stack.imgur.com/tkya7.png)](https://i.stack.imgur.com/tkya7.png)

This is an example of what is in 'digital €'

[![enter image description here](https://i.stack.imgur.com/Lc8em.png)](https://i.stack.imgur.com/Lc8em.png)

Any help would be greatly appreciated.
My code is as follows 

```
Sub EurosToPounds()

Dim rng As Range

If Sheets("Pricing Matrix").Range("InPounds") = "TRUE" Then
Exit Sub

ElseIf Sheets("Pricing Matrix").Range("InPounds") = "False" Then

For Each rng In Worksheets("Digital €").Range("Digital")
  If rng = Range("Euro1") Then
      rng.Value = Range("Euro1").Offset(0, 2).Value
  ElseIf rng = Range("Euro2") Then
      rng.Value = Range("Euro2").Offset(0, 2).Value
  ElseIf rng = Range("Euro3") Then
      rng.Value = Range("Euro3").Offset(0, 2).Value
  ElseIf rng = Range("Euro3") Then
      rng.Value = Range("Euro3").Offset(0, 2).Value
  ElseIf rng = Range("Euro4") Then
      rng.Value = Range("Euro4").Offset(0, 2).Value
  ElseIf rng = Range("Euro5") Then
      rng.Value = Range("Euro5").Offset(0, 2).Value
  ElseIf rng = Range("Euro6") Then
      rng.Value = Range("Euro6").Offset(0, 2).Value
  ElseIf rng = Range("Euro7") Then
      rng.Value = Range("Euro7").Offset(0, 2).Value
  ElseIf rng = Range("Euro8") Then
      rng.Value = Range("Euro8").Offset(0, 2).Value
  ElseIf rng = Range("Euro9") Then
      rng.Value = Range("Euro9").Offset(0, 2).Value
  ElseIf rng = Range("Euro10") Then
      rng.Value = Range("Euro10").Offset(0, 2).Value
  ElseIf rng = Range("Euro11") Then
      rng.Value = Range("Euro11").Offset(0, 2).Value
  ElseIf rng = Range("Euro12") Then
      rng.Value = Range("Euro12").Offset(0, 2).Value
  End If
Next

 Sheets("Digital €").Select
 Sheets("Digital €").Range("Digital").Select
 Selection.NumberFormat = _
    "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"

Sheets("Pricing Matrix").Select
Worksheets("Pricing Matrix").Range("InEuros") = "TRUE"

Sheets("Pricing Matrix").Select
Worksheets("Pricing Matrix").Range("InPounds") = "FALSE"

End If
End Sub

```

This code causes the numbers to change to... [![enter image description here](https://i.stack.imgur.com/WDdU9.png)](https://i.stack.imgur.com/WDdU9.png)

Another method suggested is as follows...

```
Sub EurosToPounds()

Dim rng As Range

If Sheets("Pricing Matrix").Range("InPounds") = "TRUE" Then
Exit Sub

ElseIf Sheets("Pricing Matrix").Range("InPounds") = "False" Then

For Each rng In Worksheets("Digital €").Range("Digital")
Dim foundRange As Range
Set foundRange = Range("EuroRange").Find(rng.Value) ' EuroRange should contain (
'                                                     Euro1, Euro2,.. etc).
If Not foundRange Is Nothing Then rng.Value = foundRange.Offset(0, 2).Value
Next

Sheets("Digital €").Select
Sheets("Digital €").Range("Digital").Select
Selection.NumberFormat = _
    "_-[$£-809]* #,##0.00_-;-[$£-809]* #,##0.00_-;_-[$£-809]* ""-""??_-;_-@_-"

Sheets("Pricing Matrix").Select
Worksheets("Pricing Matrix").Range("InEuros") = "TRUE"

Sheets("Pricing Matrix").Select
Worksheets("Pricing Matrix").Range("InPounds") = "FALSE"

End If
End Sub

```

However that results in this...

[![enter image description here](https://i.stack.imgur.com/vxWYc.png)](https://i.stack.imgur.com/vxWYc.png)

I feel as tho the second method may not be working as "Digital €" contains formulas in some of the cells equaling the cell to the left of it maybe? Is there a way to ignore cells with formulas in, that may solve it. Again thanks for any help!