Help with Code

[Randy]

Hello...I have a code that keeps getting a runtime 1004 error. I beleive it
is because the string is too long. Anyone have any suggestions on how to
shorten the string or expand the default range? Here is my code....

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String =
"M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12,M13,M14,M15,M16,M17,M18,M19,M20,M21,M22,M23,M24,M25,M26,M27,M28,M29,M30,M31,M32,M33,M34,M35,M36,M37,M38,M39,M40,M41,M42,M43,M44,M45,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12,N13,N14,N15,N16,N17,N18,N19,N20,N21,N22,N23,N24,N25,N26,N27,N28,N29,N30,N31,N32,N33,N34,N35,N36,N37,N38,N39,N40,N41,N42,N43,N44,N45"
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range(sINPUTS), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub

 

[Don Guillett]

Not quite sure what you are doing but try this??

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set myrng = Range("m2:m45,n2:n45")
'or more simply
'Set myrng = Range("m2:n45")

With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Target, myrng) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sINPUTS As String = "M2:M45, N2:N45"
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range(sINPUTS), .Cells) Is Nothing Then
If Not IsEmpty(.Value) Then
On Error Resume Next
Application.EnableEvents = False
.Value = "X"
End If
Application.EnableEvents = True
On Error GoTo 0
End If
End With
End Sub


Gord Dibben MS Excel MVP

 

[Randy]

Thanks guys....Gord I tried your version prior but for some reason didn;t
work...this time it did! I thank you for your assistance!