How to use uppercase in an additional column?

I am trying to add one more column to my macro. So far I have this:

Code:

---


Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range
  If Not Intersect(Range("j2:o1000,r2:ab1000"), Target) Is Nothing Then
    Application.ScreenUpdating = False
    Application.EnableEvents = False
For Each rng In Intersect(Range("j2:o1000,r2:ab1000"), Target)
rng.Value = UCase(rng.Value)
Next rng
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub



---

It works great, Capitalizing anything in the J2:O1000 and R2:ab1000. but now I want to add E3:e1000 as well. How do i do this?

Figured it out, I was only adding it to the For Each rng... line, but not adding it to the If Not Intersect... line.:doh:

Don't you just want this code?

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
   
    On Error GoTo wsc_exit

    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
    If Target.Cells.Count = 1 Then
   
        If Not Intersect(Range("E3:E1000, J2:O1000,R2:AB1000"), Target) Is Nothing Then
   
            Target.Value = UCase(Target.Value)
        End If
    End If
   
wsc_exit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

---

Bob Phillips, thanks for the alternate option. However, a new problem has surfaced. The macro doesn't work at all, not the old version or the new version that you wrote. I should point out that it stopped working after playing around with my conditional formatting. However, I have taken the drastic step of deleting all of those in an effort to figure out what the problem is. Also the problem surfaced before I even tried your code. What would cause my macro to stop working? the other macros still work, so I didn't "turn off macros..." somehow.

Here is all the macros in Sheet1:

Code:

---

Private Sub CommandButton1_Click()
' Sort button
    Range("b1").sort Key1:=Range("b4"), Order1:=xlAscending, Key2:=Range("h1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Sub CommandButton_Bottom_row_Click()
' Move_To_bottom_Row Macro
' Keyboard Shortcut: Ctrl Shift + Z
    Worksheets("Coordinator list").Activate
    Range("c1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, -1).Activate
End Sub

Sub CommandButtonUP_Top_Row_Click()
' Move_To_top_Row Macro
    Worksheets("Coordinator list").Activate
    Range("b3").Select
End Sub

Sub CommandButton_LAST_column_Click()
' Move to last column
    ActiveCell.EntireRow.Range("x1").Activate
End Sub

Sub CommandButton_First_Column_Click()
' Move to first column
    ActiveCell.EntireRow.Range("a1").Activate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   
    On Error GoTo wsc_exit

    Application.ScreenUpdating = False
    Application.EnableEvents = False
   
    If Target.Cells.Count = 1 Then
   
        If Not Intersect(Range("E3:E1000, J2:O1000,R2:AB1000"), Target) Is Nothing Then
   
            Target.Value = UCase(Target.Value)
        End If
    End If
   
wsc_exit:
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

---

Code:

---

Private Sub CommandButton1_Click()
' Sort button
Range("b1").sort Key1:=Range("b4"), Order1:=xlAscending, Key2:=Range("h1"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

Sub CommandButton_Bottom_row_Click()
' Move_To_bottom_Row Macro
' Keyboard Shortcut: Ctrl Shift + Z
Worksheets("Coordinator list").Activate
Range("c1").Select   
Selection.End(xlDown).Select   
ActiveCell.Offset(1, -1).Activate
End Sub

Sub CommandButtonUP_Top_Row_Click()
' Move_To_top_Row Macro
Worksheets("Coordinator list").Activate Range("b3").Select
End Sub

Sub CommandButton_LAST_column_Click()
' Move to last column
ActiveCell.EntireRow.Range("x1").Activate
End Sub

Sub CommandButton_First_Column_Click()
' Move to first column
ActiveCell.EntireRow.Range("a1").Activate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)       
On Error GoTo wsc_exit
Application.ScreenUpdating = False   
Application.EnableEvents = False       
If Target.Cells.Count = 1 Then           
If Not Intersect(Range("E3:E1000, J2:O1000,R2:AB1000"), Target) Is Nothing Then               
Target.Value = UCase(Target.Value)       
End If   
End If   
wsc_exit:   
Application.EnableEvents = True   
Application.ScreenUpdating = True
End Sub

---

My guess is that you have somehow turned events off. Try typing this directly into the immediate window in the VBIDE

Application.EnableEvents = True

Other than that, I would need to see the workbook.