VBA help...Excel keeps crashing

[Aphten]

Hi there,

I am trying to do multiple things and I am having trouble figuring this out.

I have a number of cells that I need auto capitalized and I have a number of cells that need to automatically change to proper case.

This is what a friend came up with and it seems to be working without issues.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RNG As Range
Dim cel As Range
Set RNG = Intersect(Range("J3,AT2,Y76,AG76,AO76,AU76,AI4"), Target)

If Not RNG Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
For Each cel In RNG
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.EnableEvents = True
Application.ScreenUpdating = True

End If

If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub

On Error Resume Next

If Not Intersect(Target, Range("C29:C55,V28:V55,AI3,H7,Y5,AC5")) Is Nothing Then
Application.EnableEvents = False
Target = UCase(Target)
Application.EnableEvents = True

End If
End Sub

I am also needing to add in some "if this cell = this, then this cell = this other thing, but every time I add it in, excel either keeps crashing or all the coding stops working or both. Yay.

This is what I am trying to add in there:

If Range("B3").Value = "A" Then
Range("C3").Value = "EF"
End If

If Range("B4").Value = "A" Then
Range("C4").Value = "JH"
End If

If Range("B5").Value = "A" Then
Range("C5").Value = "TUY"
End If

Lastly, I will add that I do have some conditioning formatting as well...not sure if that makes a different or not. If I have conditional formatting for B3, will this cause problems? So B3 makes C3 = EF when it is A, but then I also have conditional formatting so that it will fill a certain color depending on what letter it is.

 

[StephenCrump]

It would help if you posted the full code you're using, including your additions.

But I am guessing that this might help:

Application.EnableEvents = False
If Range("B3").Value = "A" Then Range("C3").Value = "EF"
If Range("B4").Value = "A" Then Range("C4").Value = "JH"
If Range("B5").Value = "A" Then Range("C5").Value = "TUY"
Application.EnableEvents = True

If you make changes to a worksheet in Sub Worksheet_Change, you need to wrap the code this way (as your friend did) so that the change doesn't trigger recursive calls on the Sub.

 

[Peter_SSs]

@Aphten
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.