ENSURE UPPERCASE STATUS AFTER ENTERING DATA

[CAROM16]

Want all input data to be in uppercase for 3 columns - named ranges are: COMMENTS, MARK, and GRADE. Have the following VBA macro which tests for input in these cells and changes lowercase (if any) to uppercase after exiting cell. Macro works great for 1 range, however, when I code all 3 ranges in the macro, than the code doesn't change to uppercase for any of the 3 ranges. I'm assuming I'm doing something wrong with the code but I can't seem to figure it out. Would greatly appreciate any help with respect to this issue. Thank you and regards.

THIS MACRO WORKS PERFECTLY FOR ALL CELLS IN THE "COMMENTS" RANGE.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("COMMENTS")) Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
End Sub

HOWEVER, MACRO DOESN'T WORK FOR ANY OF THE RANGES AFTER ADDING THE "MARK" AND "GRADE" RANGES.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Range("COMMENTS"), Range("MARK"), Range("GRADE")) Is Nothing) Then
With Target
If Not .HasFormula Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End With
End If
End Sub

Once again. thank you for your time and consideration in this matter.

 

[Akuini]

Try using Union:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Union(Range("COMMENTS"), Range("MARK"), Range("GRADE"))) Is Nothing) Then
    With Target
    If Not .HasFormula Then
        Application.EnableEvents = False
        .Value = UCase(.Value)
        Application.EnableEvents = True
    End If
    End With
End If
End Sub

 

[CAROM16]

Try using Union:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Application.Intersect(Target, Union(Range("COMMENTS"), Range("MARK"), Range("GRADE"))) Is Nothing) Then
    With Target
    If Not .HasFormula Then
        Application.EnableEvents = False
        .Value = UCase(.Value)
        Application.EnableEvents = True
    End If
    End With
End If
End Sub

WORKS LIKE A CHARM!!! THANK YOU, THANK YOU, THANK YOY!!!!!!!!!!!!!!!!!!!!

 

[Akuini]

You're welcome.
To make sure it won't be executed when multiple cells changes at the same time (it could happen when you copy-paste multiple cells), add the blue line:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge > 1 Then Exit Sub
If Not (Application.Intersect(Target, Union(Range("COMMENTS"), Range("MARK"), Range("GRADE"))) Is Nothing) Then
    With Target
    If Not .HasFormula Then
        Application.EnableEvents = False
        .Value = UCase(.Value)
        Application.EnableEvents = True
    End If
    End With
End If
End Sub