1

I am running code inVBA to test if the cell to the right is less than 0, and if it is, then to make the active cell 0. This should run when the cell is entered. Here's what I have so far:

Private Sub worksheet_change(ByVal target As Range)  
If Not Intersect(target, Range("c7:c27")) Is Nothing Then isNegative  
End Sub

and then

Sub isNegative()  
Dim cell As Range  
If ActiveCell.Offset(1, 0).Value < 0 Then  
ActiveCell.Value = 0  
End If  
End Sub

But, what I assume is happening is that it checks the data before calculating. How do I fix this?

Nazik
  • 8,696
  • 27
  • 77
  • 123
lebout
  • 85
  • 1
  • 10

1 Answers1

2

This should run when the cell is entered.

Like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim aCell As Range

    If Not Intersect(Target, Range("C7:C27")) Is Nothing Then
        For Each aCell In Range("C7:C27")
            If aCell.Offset(1, 0).Value < 0 Then aCell.Value = 0
        Next
    End If
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Ok I had run this and had it working, but I thought it could be more efficient by not running it on every cell. Is there any way to do this more efficiently? – lebout Apr 24 '13 at 14:08
  • @user2232633 the `Worksheet_change` event fires on every cell `change`, too, so this would not be any less efficient. – David Zemens Apr 24 '13 at 14:09
  • @user2232633 : `If Not Intersect(Target, Range("C7:C27")) Is Nothing Then` takes care that it will not get too deep in the code... – Siddharth Rout Apr 24 '13 at 14:09
  • @user2232633: You have to use `Worksheet_SelectionChange` instead of `Worksheet_change` as the latter runs only if a change is made to the cell and will not fire if you just `ENTER` the cell. – Siddharth Rout Apr 24 '13 at 14:14
  • @Siddharth Rout this works thanks! I mixed up row and column; should have been aCell.Offset(0,1) but this works. Thanks again! – lebout Apr 24 '13 at 14:21
  • @user2232633: Glad to be of help :) – Siddharth Rout Apr 24 '13 at 15:22