Macro/VBA Help

[DoosanRuss]

Good Day All

I've knocked up some code, after reviewing countless posts on this page and it's working hoorah!
I've just thought of a problem though; if the user changes the currency outside of my selected three, USD, GBP & EUR then my code will fail and i'll have to go in and change the Currencies each time.
(note: I know i could put all the options - over 100 Currencies, but there must be a better way)

Is there a way you can tell the code to do the following?

i.e Currently
A1 = USD, B1=GBP, C1=EUR

if these change to
A1=CLP, B1=EUR, C1=BRL

can the code be pointed at the cells that have changed instead of typing the new currency code like "CLP" , "EUR" & "BRL" ?

Current Code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim myPlage As Range
    Dim cell As Range, keyRange As Range
    
    Set myPlage = Range("C3:L9")
    Set keyRange = Application.Intersect(myPlage, Target)
    
    If Not keyRange Is Nothing Then
        For Each cell In keyRange
            Select Case cell.Value
                Case Is = "GBP"
                    cell.Interior.ColorIndex = 10
                    cell.Offset(0, -1).Interior.ColorIndex = 10
                Case Is = "USD"
                    cell.Interior.ColorIndex = 12
                    cell.Offset(0, -1).Interior.ColorIndex = 12
                Case Is = "EUR"
                    cell.Interior.ColorIndex = 37
                    cell.Offset(0, -1).Interior.ColorIndex = 37
                Case Else
                    cell.Interior.ColorIndex = xlNone
                    cell.Offset(0, -1).Interior.ColorIndex = xlNone
            End Select
        Next cell
    End If

End Sub

I Appreciate this looks like it could be CF, but it needs to be VBA as I'm utilising the ozgrid/Chip Pearson (whom ever) SumColor UDF... which is amazing

 

[MrKowz]

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim myPlage As Range
    Dim cell As Range, keyRange As Range
    
    Set myPlage = Range("C3:L9")
    Set keyRange = Application.Intersect(myPlage, Target)
    
    If Not keyRange Is Nothing Then
        For Each cell In keyRange
            Select Case cell.Value
                Case Range("A1").Value
                    cell.Interior.ColorIndex = 10
                    cell.Offset(0, -1).Interior.ColorIndex = 10
                Case Range("A2").Value
                    cell.Interior.ColorIndex = 12
                    cell.Offset(0, -1).Interior.ColorIndex = 12
                Case Range("A3").Value
                    cell.Interior.ColorIndex = 37
                    cell.Offset(0, -1).Interior.ColorIndex = 37
                Case Else
                    cell.Interior.ColorIndex = xlNone
                    cell.Offset(0, -1).Interior.ColorIndex = xlNone
            End Select
        Next cell
    End If

End Sub

 

[DoosanRuss]

Cheers MrKowz

much obliged, i think maybe next time, I'll go grab coffee then come back to it.

the simple ones are the best,

But i thank you anyway Sir