mloucel
New Member
- Joined
- Feb 22, 2016
- Messages
- 37
I have this code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim myRange As Range
' Set range to check this are the cells that have the Notes
' Note1 Note2 Note3
Set myRange = Range("AG2:AG1500, AI2:AI1500, AK2:AK1500")
' Only run code if cell is in selected range
If Intersect(Target, myRange) Is Nothing Then
Exit Sub
Else
' If column Note has data and column Date is blank, insert current date in column Date
If Len(Target) > 0 And Len(Target.Offset(0, -1)) = 0 Then
Target.Offset(0, -1) = Date
Else
' If column Note is blank and column Date has data, remove data from column Date
If Len(Target) = 0 And Len(Target.Offset(0, -1)) > 0 Then
Target.Offset(0, -1).ClearContents
End If
End If
End If
Set otherRange = Range("G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500, AB2:AB1500, AG2:AG1500, AI2:AI1500, AK2:AK1500")
If Intersect(Target, otherRange) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub
The routine has to check this range of cells ( G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500, AB2:AB1500, AG2:AG1500, AI2:AI1500, AK2:AK1500 ) and convert all of them to Uppercase.
But as you can see ONLY AB2:AB1500, AG2:AG1500, AI2:AI1500, AK2:AK1500 actually work, the rest ( G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500 ) is simply ignored and I am out of IDEAS
Can someome be so kind to explain to me what the heck am I doing wrong..
I am fairly NEWBIE in VBA so my apologies any help will be greatly appreciated..
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim myRange As Range
' Set range to check this are the cells that have the Notes
' Note1 Note2 Note3
Set myRange = Range("AG2:AG1500, AI2:AI1500, AK2:AK1500")
' Only run code if cell is in selected range
If Intersect(Target, myRange) Is Nothing Then
Exit Sub
Else
' If column Note has data and column Date is blank, insert current date in column Date
If Len(Target) > 0 And Len(Target.Offset(0, -1)) = 0 Then
Target.Offset(0, -1) = Date
Else
' If column Note is blank and column Date has data, remove data from column Date
If Len(Target) = 0 And Len(Target.Offset(0, -1)) > 0 Then
Target.Offset(0, -1).ClearContents
End If
End If
End If
Set otherRange = Range("G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500, AB2:AB1500, AG2:AG1500, AI2:AI1500, AK2:AK1500")
If Intersect(Target, otherRange) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Value = UCase(Target.Value)
Application.EnableEvents = True
End Sub
The routine has to check this range of cells ( G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500, AB2:AB1500, AG2:AG1500, AI2:AI1500, AK2:AK1500 ) and convert all of them to Uppercase.
But as you can see ONLY AB2:AB1500, AG2:AG1500, AI2:AI1500, AK2:AK1500 actually work, the rest ( G2:G1500, H2:H1500, N2:N1500, R2:R1500, T2:U1500 ) is simply ignored and I am out of IDEAS
Can someome be so kind to explain to me what the heck am I doing wrong..
I am fairly NEWBIE in VBA so my apologies any help will be greatly appreciated..