Select the next column and start on line 4 when a condition is satisfied.

**Lifeseeker** · 02-10-2012, 09:43 AM

It works, but it almost seems counter-intuitive though...I mean it doesn't prompt you the moment you put in a text value in there. It's only when you go back to the cell and then it will tell you you have put in a letter instead of a number.

That's why I had thought I would put that check in the worksheet change event, but it didn't work...

**tom1977** · 02-10-2012, 09:51 AM

It works exactly what You wished for
the msbox appears when You just put text in range and accept this with "enter" or select next cell with mouse click

I do not follow...

Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Row > 4 And Target.Row < 11 Then
    If Not IsNumeric(Target) Then
        Target.ClearContents
        MsgBox "this is not a number!"
    End If
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Cells.Interior.ColorIndex = xlNone
    Cells.Font.Size = 14
    Cells(2, Target.Column).Interior.ColorIndex = 27
    Cells(Target.Row, 1).Interior.ColorIndex = 26
    
    If Target.Count = 1 Then
    With Target
        .Interior.ColorIndex = 43
        .Font.Size = 18
    End With
    End If
    
    If Target.Row > 11 Then
    Cells(4, Target.Column + 1).Select
    Cells(2, Target.Column + 1).Interior.ColorIndex = 27
    End If
    
End Sub

p.s I am going home so this is my last post for couple houres

**Lifeseeker** · 02-10-2012, 10:00 AM

But when you hit "Enter" or move to next cell, it doesn't tell you right away you have just put in a wrong format. It's only when you go back to the cell it can then prompt you. This is the difference in effect between the worksheet_change and worksheet_selection change.