If not intersect code

[erutherford]

On this sheet there are two command buttons and code for SelectionChange. Here is the code for the SelectionChange:

 If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If Target.Cells.Value = " " Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "4" Then Target.Offset(0, 1).Select
    End If

This is what I am trying to do. If you enter a number 4 in column H, you are automatically taken to Col. I same row. I'll add a Msgbox telling the user to enter the required data, but for now I just trying to do the above.

I have this same code working fine on other sheets!

thanks

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If Target.Cells.Value = " " Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "4" Then Target.Offset(0, 1).Select
    End If
End Sub

 

[erutherford]

It worked fine, thank you. The two codes are the same, I just didn't include the header and End Sub lines in my example. I cut and pasted your sample in and perfect results. Wonder if I had left something behind that I could not see???

 

[My Aswer Is This]

Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.

It worked fine, thank you. The two codes are the same, I just didn't include the header and End Sub lines in my example. I cut and pasted your sample in and perfect results. Wonder if I had left something behind that I could not see???

 

[GTO]

I would note that either of these...

'Target.Cells.Value = " "
'Target.Value = "4"

...will error out (13: Type Mismatch) if multiple cells in the range returned by Intersect are changed (i.e. - select several cells and press the Del key to clear content). I would first IF test to ensure Target.Count = 1.

Hope that helps,

Mark

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("H:H")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    If Target.Cells.Value = " " Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "4" Then Target.Offset(0, 1).Select
    End If
End Sub