VBA EXCEL infinite loop - changing value of cell when that cell is modified

Viewed 103

I'm having a problem in excel VBA where I would like to do the following:

If the user enters a certain String, I want that String to be modified so that it is uppercase and then add lowercase "j" or "n" depending on which column the cell is in (jour or nuit - day or night in french)

the problem is that when I modify the cell's value, because it's then modified by the macro, the macro is called again, and that happens over and over...

Here is my code :


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
    'On Error GoTo done
    Set KeyCells = Range("T41:KC66")

    If Not Application.Intersect(KeyCells, Target) Is Nothing Then
        Dim ens As Range
        Dim cel As Range
        Set ens = Target.Cells
        Dim valeur As String
        'MsgBox "boo"
        valeur = UCase(ens(1, 1))
        For Each cel In ens
            Select Case valeur
                Case "R", "RJ", "RN"
                    If ActiveSheet.Cells(40, cel.Column) = "J" Then
                        cel.Value = "Rj"
                    Else
                        cel.Value = "Rn"
                    End If
                Case ...
                    ...
                    ...
                    
           End Select
       Next cel
    End If
End Sub

I have other cases as well but only one is needed to show the problem.

Do you guys have any solutions for me ?? Thanks! :)

0 Answers
Related