Excel macro and formula

[wwheaton]

In excel 97 I need to be able to delete a user selected row when a specified cell equals or exceeds a certain value

[Al Chara]

Try the following and post with problems:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DltRow As Integer
On Error Resume Next
If Target.Value > Range("A1").Value Then
DltRow = InputBox("Type Row Number to Delete")
Rows(DltRow).Delete
End If
End Sub

Put this code in the module for your worksheet. Target is the cell that you are comparing to Cell A1. Edit as needed.

[wwheaton]

Thanks Al, here is where i show my ignorance. I cannot get excel to see the macro to run it. I went into VB editor and added the code as shown but how do i run it?

[Al Chara]

This is not a macro that you run. It is an event procedure that will run, whenever your worksheet is changed. I am not sure if it is what you want. But to test it, follow the following procedure:

1. In excel hit, Alt+F11 to go into VBA
2. Look to the left in the project explorer and double click your worksheet where you want the fun to happen.
3. Look to the right for the big blank space and paste in the code.
4. Close VBA and try entering in numbers.

[wwheaton]

Al, that exactly what i want it to do. I'm sorry but Assume i want to compare cell b1 to a1 and have the event happen when b1 is equal or greater than a1. I'm not sure where to edit it to make this happen. Again I'm not really used to working in VB and with other than simple ( really simple) code. Thanks for your patience.

[Al Chara]

Try the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DltRow As Integer
On Error Resume Next
If Target.Address = "$B$1" And Target.Value >= Range("A1").Value Then
DltRow = InputBox("Type Row Number to Delete")
Rows(DltRow).Delete
End If
End Sub

_________________
Hope this helps.
Kind regards, Al.


[ This Message was edited by: Al Chara on 2002-04-16 10:23 ]

[wwheaton]

Thanks again and sorry to keep bugging you. Entered the code as shown - typed it in by hand. No errors. Went to cell a1 and entered a number(5) and then went to b1 and entered a larger number(10) and nothing happened. What am i doing wrong? Can you email me a spread sheet with an example? Email address is wayne.wheaton@eds.com

Thanks again