VBA Help

[APML]

Hi All. I'm not so good with VBA. I have the following code which changes lower case to upper case. I need to add a lot more cells into the range but it's getting difficult to
work with. Just want help to add an additional line in so I can add more cells into the range (and still be able to read it).

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not (Application.Intersect(Range("B5"), Target) Is Nothing) Then _
ThisWorkbook.Sheets(Target.Value).Activate



If Not (Application.Intersect(Target, Range("$O$2:$O$3,$C$11:$C$12,$C$35:$C$36,$C$59:$C$60,$C$83:$C$84,$C$107:$C$108,$C$131:$C$132,$C$155:$C$156,$C$179:$C$180")) _
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

 

[Jeffrey Mahoney]

My suggestion is to create a named range that includes all the cells you want to change. Then you don't have to change the range reference in VBA, you can just add the cells you need in the named range menu.

 

[6StringJazzer]

Is there any rhyme or reason to which cells are affected? Seems like a pretty random list based on location. There might be a way to check if the cell is eligible besides just the brute force of listing all of them.

You could also do this:

If Not (Application.Intersect(Target, Range("$O$2:$O$3,$C$11:$C$12,$C$35:$C$36,$C$59:$C$60," & _
                                            "$C$83:$C$84,$C$107:$C$108,$C$131:$C$132," & _
                                            "$C$155:$C$156,$C$179:$C$180")) _
Is Nothing) Then

 

[APML]

Hey, thanks all. Jeffrey, what a great idea, I never considered naming the ranges I use in VBA's, it makes so much sense, particularly when I'm adding or
deleting rows. Thanks for the tip