Worksheet Change Q

S

Seanie

How can I select multiple ranges within a Change event code? I've
tried below but I hit debug

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range("J10:J22", "J28:J40",
"J46:J58", "J64:J76", "J82:J94", "J100:J112", "J118:J130")) Is Nothing
Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub
 
B

Bob Phillips

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const WS_RANGE As String = _
"J10:J22,J28:J40,J46:J58,J64:J76,J82:J94,J100:J112,J118:J130"
With Target
If .Count = 1 Then
If Not Intersect(.Cells, Range(WS_RANGE)) Is Nothing Then
Application.EnableEvents = False
.Value = UCase(.Value)
Application.EnableEvents = True
End If
End If
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Change_Event troubles, no error & no work. 6
Date Stamp 4
Trying to run excel vba code when cell value changes from empty to entered value. 1
Event to add a date if "X" is entered in a cell 3
2 Macros in VBA??? 3
Place a date in another cell only if adjacent cell = "X" 1
Help with Sub's on a worksheet 4

Top