VBA Comparer 2 cellules sur 2 feuilles differentes

Sub ChampagneToGeneral()
'on récupère la liste des cabines pour lesquelles il faut une bouteille de champagne
Set Champ4CabinList = Sheets("Champagne").Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)

'Champ4CabinList.Select
Set TabGeneral = Sheets("GENERAL").Range("L11:AA" & Range("L" & Rows.Count).End(xlUp).Row)
'TabGeneral.Select

For Each cabin In Champ4CabinList
    With TabGeneral
        Set ici = .Find(cabin, lookat:=xlWhole)
            If Not ici Is Nothing Then
                ici.Select
                ici.Interior.ColorIndex = 48
            End If
    End With
Next cabin

End Sub

Sub WaterToGeneral()
Application.Calculation = xlCalculationManual 'pour empecher l'exécution de la fonction personalisée qui ralentit cette macro
'on récupère la liste des cabines pour lesquelles il faut de l'eau
Set Water4CabinList = Sheets("water").Range("A2:B" & Range("B" & Rows.Count).End(xlUp).Row)

'Water4CabinList.Select
Set TabGeneral = Sheets("GENERAL").Range("L11:AA" & Range("L" & Rows.Count).End(xlUp).Row)
'TabGeneral.Select

For Each cabin In Water4CabinList.Columns(2).Cells
    With TabGeneral
        Set ici = .Find(cabin, lookat:=xlWhole)
            If Not ici Is Nothing Then
                ici.Offset(-1, 0) = cabin.Offset(0, -1)
                End If
    End With
Next cabin
Application.Calculation = xlCalculationAutomatic 'on réactie le mode de calcul automatique
End Sub

Sub main()

  With Worksheets(GetSheetName(Range("D:D").Value, Range("F:F").Value))
  MsgBox .Name
  End With

End Sub
[code]

[code]
Function GetSheetName(val1 As String, val2 As String) As String
  Select Case GetRank(UCase(val1)) - GetRank(UCase(val2))
  Case Is < 0
  GetSheetName = "GOLD"
  Case Is > 0
  GetSheetName = "Platin"
  Case Else
  GetSheetName = val1
  End Select
End Function
[code]

[code]
Function GetRank(val As String) As Long
  GetRank = Application.WorksheetFunction.Match(val, Array("Bronze", "Silver", "Gold", "Platin", "PlPlus", "Ambass"))
End Function
Function GetRank(val As String) As Long
  GetRank = Switch(val = "BRONZE", 0, _
  val = "SILVER", 1, _
  val = "GOLD", 2, _
  val = "PLATIN", 3, _
  val = "PLPLUS ", 4, _
  val = "AMBASS", 5)
End Function
[code]

[code]
Function GetRank(val As String) As Long
  Select Case val
  Case "BRONZE"
  GetRank = 0
  Case "SILVER"
  GetRank = 1
  Case "GOLD"
  GetRank = 2
  Case "PLATIN"
  GetRank = 3
  Case "PLPLUS"
  GetRank = 4
  Case "AMBASS"
  GetRank = 5
  End Select
End Function