Replacing the value of a cell with list validation where the list values are updated with an Excel formula

[hwiderst]

Hi there,
I'm a complete novice to VBA and therefore they question is probably stupid, but I have to start somewhere...

Background:
On worksheet "Order Generator"
I can select a product configuration in to cells: D11 colour of the product (black/white) D12 colour of accessories (black/red/white). This is done with a dropdown list validation to a list of the 3 options on another worksheet "Calculations".
The configurator will be used in multiple countries, so I need to translate all text incl. cell values (configurations) to English and French
I do this by adding 2 buttons with French and UK flags, where - depening on which button is pressed the value of cell E4 is set to "EN" or "FR. This in turn changes all cells by using HLOOKUPS to translation sheet.

Problem:
When language is changed the values of the validation lists on the Calculation sheet are changed from let's say "black/red/white" to "noir/rouge/blanc". The dropdown list is updated OK, but the actual cell value is not. I therefore thought I'd try some VBA code.
Since the validation list values are not changed manually or by VBA but rather via the HLOOKUP function I have understood it so that I cannot use Worksheet_Change and therefore throught Worksheet_Calculate could work.
I have managed to put together the following, that does not compile - but cannot see where I go wrong. Would really appreciate some help!!!

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Dim count_cells As Integer
Dim new_value As String
Dim old_value As String
Dim rng As Range
For count_cells = 1 To Range("A1").CurrentRegion.Rows.Count - 1
Set rng = Worksheets("Order Generator").Range("D12")
Application.EnableEvents = False
new_value = Range("A" & count_cells).Value
Application.Undo
old_value = Range("A" & count_cells).Value
Range("A" & count_cells).Value = new_value
If StrComp(rng, old_value) = 0 Then
rng.Replace What:=old_value, Replacement:=new_value
Else
Next count_cells
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[rlv01]

Private Sub Worksheet_Calculate()
    Application.ScreenUpdating = False
    Dim count_cells As Integer
    Dim new_value As String
    Dim old_value As String
    Dim rng As Range
    For count_cells = 1 To Range("A1").CurrentRegion.Rows.Count - 1
        Set rng = Worksheets("Order Generator").Range("D12")
        Application.EnableEvents = False
        new_value = Range("A" & count_cells).Value
        Application.Undo
        old_value = Range("A" & count_cells).Value
        Range("A" & count_cells).Value = new_value
        If StrComp(rng, old_value) = 0 Then
            rng.Replace What:=old_value, Replacement:=new_value
        Else '<--------------------------------------------------- this is why it is not compiling
        Next count_cells
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub

The "else" statement is why it is not compiling. Change that to "End If" and it will compile. I'm not sure if it will do what you want however.

Have you considered a simpler approach? Maybe something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$4" Then
        Application.EnableEvents = False
        Select Case Target.Value
        Case "EN"
            Me.Range("D12").Replace What:="black", Replacement:="noir", MatchCase:=True
            Me.Range("D12").Replace What:="red", Replacement:="rouge", MatchCase:=True
            Me.Range("D12").Replace What:="white", Replacement:="blanc", MatchCase:=True
        Case "FR"
            Me.Range("D12").Replace What:="noir", Replacement:="black", MatchCase:=True
            Me.Range("D12").Replace What:="rouge", Replacement:="red", MatchCase:=True
            Me.Range("D12").Replace What:="blanc", Replacement:="white", MatchCase:=True
        End Select
        Application.EnableEvents = True
    End If
End Sub

 

[AlphaFrog]

I do this by adding 2 buttons with French and UK flags, where - depending on which button is pressed the value of cell E4 is set to "EN" or "FR. This in turn changes all cells by using HLOOKUPS to translation sheet.

Instead of using an event procedure like Worksheet_Calculate or Worksheet_Change, just have the code in the buttons translate the Data Validation cells after it changes cell E4. You can translate the DV cells using code similar to what rlv01 suggested.

 

[hwiderst]

Thanks x 1000 guys!
This did the trick. I added code in the buttons as you suggested AlphaFrog and tweaked the code from rlv-01... and it worked !