If col A <= 0 Then

[wisewood]

I am after a way, when the worksheet is changed, for VBA to check if column A on the row that was changed is <= 0 then set the value of column D to nothing, empty, blank ... "".

Can someone help me.

I have managed to do something with it, but it seems to check every row when something is changed, rather than just the row containing the cell that was altered.

 

[brian.wethington]

Are you trying to check A of the row and if it is less than or equal to 0 then make the column d of that row nothing?

You can do this using a Worksheet Event

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub
If Cells(Target.Row, 1).Value <= 0 Then
    Cells(Target.Row, 4).Value = vbNullString
End If
Application.EnableEvents = True

End Sub

This will need to be put in the sheet you will be using it on.

 

[wisewood]

Yes, that's the code that i had myself.

However, it does seem to flicker the screen for a few seconds each time you change a cell, as though it is performing this check against every row.

 

[brian.wethington]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Count > 1 Then Exit Sub
If Cells(Target.Row, 1).Value <= 0 Then
    Cells(Target.Row, 4).Value = vbNullString
End If
Application.ScreenUpdating = False
Application.EnableEvents = True

End Sub

This might fix a bit of the flicker as well.

 

[wisewood]

Excellent. Thanks.