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! :)