clear contents re value

[Curt]

Want entry in column 16 to clear if value in column 12 is >= to 10
When entry is made into column 12 as target calls sub pledge following.

If target.column = 12 And target.Value >= 10 And IsNumeric(target.Value)
Then _
Call Pledge(target)

Public Sub Pledge(ByVal target As range)
Sheets("data").Select
target("column = 16").ClearContents
End Sub

Know I am missing something all entries in same row and will apply thru
complete sheet. Reason I believe (target) keeps column row relationship.

 

[JLGWhiz]

You can put this in the sheet code module by right clicking the sheet tab
then select View Code from the pop up menu and paste this into the window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = Cells(Rows.Count, 12).End(xlUp).Row
If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
Sheets("data").Range("P" & Target.Row).ClearContents
End If
End If
End Sub

This eliminates the need to call the othe macro. If the criteria is met, it
goes directly to the sheet and deletes clears the range in column 16 for the
same row as the target in column 12. Let me know if you get an error and
what the error message says.

 

[Curt]

If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
syntax error
tried putting underscore after Then no go

 

[Curt]

expected list seperator or ) also as error

You can put this in the sheet code module by right clicking the sheet tab
then select View Code from the pop up menu and paste this into the window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = Cells(Rows.Count, 12).End(xlUp).Row
If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
If IsNumeric(Target.Value) And Target.Vaue >= 10 Then
Sheets("data").Range("P" & Target.Row).ClearContents
End If
End If
End Sub

This eliminates the need to call the othe macro. If the criteria is met, it
goes directly to the sheet and deletes clears the range in column 16 for the
same row as the target in column 12. Let me know if you get an error and
what the error message says.

 

[Curt]

If Not Intersect(Target, Range("L2:L" & lr) Is Nothing Then
did catch missing ) after Target, Range still didn't clear "p"

 

[JLGWhiz]

I had two typos in it. I tested it and it did clear the range in P.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = Cells(Rows.Count, 12).End(xlUp).Row
If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value >= 10 Then
Sheets("data").Range("P" & Target.Row).ClearContents
End If
End If
End Sub

 

[Curt]

copyed it and worked fine Cells row count can be expanded I think will do it
if ok sheeet may have many rows used 65536 to cover all possible. If this
makes sense I 'm in your debt
If we didn't make typos we wouldn't be human
thanks

 

[Curt]

I may have left something out My entries start in row 5 works ok with row
count set at 12. If i increase row count nbr stops working worksheet may have
up to 200 rows of entries.
lr = Cells(Rows.Count, 12).End(xlUp).Row
do we need to change intersect range
If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then

I have this in a copy paste sub
Set rngPaste = wksSummary.Cells(65536, "A").End(xlUp).Offset(1, 0)
Been working on this program for 2 years now I am only retired one on Vet's
parade comittee who stepped up to do this. Without people like you us old
timers would never have been able to get it done. Sure made last year go good
had 33 entries on parade route in 10 minutes.
Really appreciate the help.

 

[Curt]

Got it by makeing change in both lines
Thanks again

 

[JLGWhiz]

This line:

lr = Cells(Rows.Count, 12).End(xlUp).Row

is to find the row number of the last cell with data in column 12 ("L"). By
using the variable "lr" as the row number, it allows the range to
automatically expand in code as the user adds to or eliminates rows of data.
To change the beginning cell of the range:

If Not Intersect(Target, Range("L2:L" & lr)) Is Nothing Then

Change the L2 in this line of code to L5. The code would then read:

If Not Intersect(Target, Range("L5:L" & lr)) Is Nothing Then

which in plain language means a cell somewhere from row five of column L to
the last row with data in column L.

Have a great parade. We old timers have to stick together.